THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Linchi Shea

Checking out SQL Server via empirical data points

SQL Server and SANs: The QueueDepth Setting of a Host Bus Adapter (HBA)

Too many DBAs tend to view a drive presented from a Storage Area Network (SAN) as something of a monolithic nature. They look at the drive as if it had some intrinsic performance characteristics. This view doesn't help one appreciate the true performance characteristics of such a drive.

A more constructive view is to look at the drive as an I/O path that is made up of layers of hardware and software components, many of which can have a huge impact on the performance of the drive. One such component is the fibre channel Host Bus Adapter (HBA), which typically plugs into a PCI-X or PCI-Express slot on the server. An HBA can be simply viewed as a host-side controller for SAN. The performance of the drive can be affected by many HBA-related design choices such as the following:

  1. The model and make of the HBA,
  2. The theoretical throughput of the HBA,
  3. The number of HBAs and how/whether they are load balanced,
  4. The HBA driver, and
  5. The configurations of the HBA driver

In this blog post, I'll look at one specific HBA driver parameter setting called QueueDepth--the maximum number of outstanding I/O requests that can be queued at the host bus adapter. Rather than discuss this in abstract, let me look at a specific HBA--Emulex LightPulse LP10000 2 Gigabit PCI Fibre Channel Adapter. This is not the fastest HBA on the market today, but has been a rather popular one. This HBA uses a Windows Storport Miniport driver (version 5-1.11A0) whose QueueDepth setting can be configured.

The question is: How does this QueueDepth HBA driver parameter affect the performance of a drive presented through the HBA?

To demonstrate the performance impact of the QueueDepth driver parameter, I ran a series of 8K random read I/O tests on the drive. For the tests, the QueueDepth parameter was set to the following values (at the host level, thus changing the QueueDepth for the two HBAs that were dynamically load balanced on the server):

  1. QueueDepth = 8,
  2. QueueDepth = 16,
  3. QueueDepth = 32,
  4. QueueDepth = 64, and
  5. QueueDepth = 128

In the tests, the I/O load level was controlled with a single thread that maintained a varying number of outstanding I/Os, i.e. workload queue depth. The I/Os per second obtained on the drive were taken as the key performance measure. The following chart shows the performance behavior of the drive with the driver parameter QueueDepth set to the above-mentioned values:

It is clear from the chart that the setting of the HBA QueueDepth parameter can have a huge performance impact on the drive, with everything else remaining the same. In particular, if you set this parameter too low, you can seriously reduce the throughput of the drive.

In general, it is recommended that this parameter be left at its vendor suggested default, which is 32 for this Emulex card. The test results appear to support the recommendation.

The key point however is that a drive presented from a SAN is far from being a monolithic entity and shouldn't be viewed as such. One needs to be aware of all the key components on its I/O path to truly appreciate its behavior.

Published Tuesday, September 18, 2007 1:07 AM by Linchi Shea
Filed under: , ,

Attachment(s): QueueDepth.gif

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Trayce Jordan said:

There is a theoretical limit in the number of IOPS the controllers of a disk subsystem can handle.  The QueueDepth for each host connected to a single disk subsystem is designed (to a certain extent) to be a govenor so that any one host (server) doesn't take up all the IO activity to the detriment of other hosts.  That is why the default is set to 32.

If you have an environment where there are very few hosts (or even a single host) connected to the same disk subsystem, then you can safely increase the QueueDepth to higher values to gain better throughput and performance.

As always, testing first is the best advice and knowing the demands from all hosts involved to predict the effects of change before actually making it.  If you have 50 hosts connecting to a single disk subsystem, then be very careful before changing these values, as the performance on other machines can drastically be reduced.

But hey, if you only have one host (server) connected, then increase it.  With Emulex you can set it as high as 255 or 256 (not sure if it's 0 or 1 based -- I don't have documentation with me any longer and my memory gets worse the older I get <smile>).

September 18, 2007 9:25 AM
 

WesleyB said:

Great post again Linchi.

There is another parameter that has impact on the QueueDepth setting and that is QueueTarget.  This determines whether the QueueDepth is for the entire array or per LUN.  Our storage team advised us to set it per LUN and therefore we have to use QueueDepth 8 since the QueueDepth is then multiplied by the number of LUNs.

Have you done any tests with this setting?  Unfortunately we have little testing opportunities at our current customer :-(

Kind regards,

Wesley

September 18, 2007 9:25 AM
 

WesleyB said:

"we have to use QueueDepth 8 since the QueueDepth is then multiplied by the number of LUNs"

At least this is what the storage team told us :-)

September 18, 2007 9:33 AM
 

Grumpy Old DBA said:

Those that know me know that I consider SAN's to be one of the major bottlenecks to high performance

September 19, 2007 5:43 AM
 

colin leversuch-roberts said:

superb post, thankyou.

September 19, 2007 5:46 AM
 

James Luetkehoelter said:

Great post, as all of yours have been - I have a question/comment about your benchmark graphs though.

It seems these are isolated in nature - have you considered measuring the same in an environment that emulates multiple threads? It seems to me that without that comparison (maybe using SQLIOStress?), we could be making some bad assumptions. I've seen very unexpected results moving from a test, controlled environment to the one of chaos that we see everyday.

Just a thought - nice post though, keep em coming :)

September 20, 2007 6:55 AM
 

Linchi Shea said:

James;

By 'multiple threads', I assume that you would like to see multiple OS threads issuing I/O requests concurrently. As far as the downstream I/O components are concerned, the number of the OS threads that are issuing I/O requests is more or less transparent. For instance, the HBA driver simply sees a stream of I/O requests that it may have to queue up.

I used sqlio.exe to obtain the numbers in the chart. You could easily control the number of threads that sqlio.exe spawns with the parameter -t.

In any event, the key point of this blog post is not the exact performance impact of the HBA setting on the drive. For instance, you would see a different impact if I use a different I/O workload. But rather, the performance impact is used to highlight the fact that the performance behavior of a drive presented from a SAN can be influenced by many factors on its I/O path, and the HBA setting is one such factor.

Linchi

September 20, 2007 10:27 AM
 

Linchi Shea said:

Grumpy Old DBA;

If you are commenting on your specific SAN setup or even the SAN setups you have worked with, that is not an uncommon observation because too many SANs are configured for general enterprise use rather than specifically for superior database performance. I just want to caution that general statements such as 'SANs are one of the major bottlenecks to high performance' without a specific context are almost certainly false. I'd argue that if you have a decent SAN infrastruture, the problem is often not the SAN, but how the SAN or the drive presented from the SAN is configured.

Linchi

September 20, 2007 10:34 AM
 

James Luetkehoelter said:

Great clarification of the intent of the article Linchi, thanks. I great post too. I'm disappointed that I missed your session at PASS....

September 20, 2007 9:10 PM
 

AaronBertrand said:

I agree that Linchi that often default SAN configurations can be a bottleneck, but it most certainly is not the SAN's fault, and if you have a decent SAN that you can configure properly for your environment and workload, there is no way it will be the bottleneck.

September 23, 2007 10:02 AM
 

Grumpy Old DBA said:

As a further post to my series on what else can affect SQL Server Performance here’s the thorny issue

April 3, 2008 7:55 AM
 

Asif said:

I had read a lot of other blogs that talked about using SQLIOSIM to performance test the SAN so that's what I did as well and got horrible numbers for the Running Average IO Duration.  Ran many tests with queue depth paraemter of 64, 128, 256 and the default which I believe was 8 and the Running Avg IO Duration never went below 50.  I did see an improvement in the IO Request Blocks number with best performance at 128.  Now I've read other articles that say not to use SQLIOSIM for performance testing the SAN.  My question is that shouldn't SQLIOSIM still give decent disk latency (Running AVG)numbers even if it's not pushing SAN to the max.  I would think that running SQLIO should give even worse number since it pushes the SAN far more than SQLIOSIM.  Any thoughts?  Will appreciate a reply back

January 25, 2010 10:44 AM
 

BlackHawk said:

Does anybody have the actual technical reference with respect to the -o parameter and SQL Server? How many outstanding requests will it allow per thread? No use testing 256 if it throttles at 16, or spawns an entirely new thread at 4.

Bob Dorr says that most SQL Server activity is ASYNC but the SQLIO ReadMe file says -o is not compatible with NT Scatter/Gather I/O. That almost makes one believe that the maximum -o setting would be 1.

It's all very confusing.

Does anyone know of a way to identify the outstanding I/O queue depth of a running thread?

March 31, 2010 10:21 AM
 

Lonny Niederstadt said:

Thought I'd throw in a few words.  When looking at queue depth from the perspective of the Windows server, there are two hard limits to keep in mind.  One limit is the maximum port queue depth supported by the target SAN port(s).  All expected outstanding IOs across all hosts should be kept below that limit; performance penalty will result otherwise.

The other limit to investigate is the per LUN maximum queue depth imposed by the storage unit.  For example,  EMC CLARiiON/VNX storage has a per LUN max queue depth of 32+(data drives*14).  A 4+1 RAID 5 and a 4+4 RAID 10 LUN each have 4 data drives, thus have maximum queue depth of 88.  If the Windows host sets the LUN queue depth to 128 and outstanding IOs rise above the storage array max queue depth, a performance penalty will result.

Most Hitachi storage documents a maximum supported LUN queue depth of 32.  Most HP storage based on Hitachi hardware also has a documented maximum queue depth of 32.

So, on Hitachi storage I would expect a performance penalty when outstanding IO per LUN exceeds 32.

IBM storage is an outlier - DS8000 series, the XIX, and possibly even SVC/Storwize line may support queue depths per LUN of 256 or more.

So the port max queue depth, and LUN max queue depth if applicable, from the storage array are important considerations in determining the Windows host maximum queue depth.

September 5, 2013 10:50 AM

Leave a Comment

(required) 
(required) 
Submit

About Linchi Shea

Checking out SQL Server via empirical data points

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement