potential SAN misconfiguration?

Question

I am a developer and know very little about Storage systems. At work, I noticed slow large query performance on one of the SQL Servers. I was told the database is on a fiber channel SAN MSA1500 (HP?) with 14 COMPAQ BF3008B26C drives. I did a quick HD tune benchmark when the server had practically no load. Average read was about 60MB/s. I don't know much about SANs but that read speed looks really low to me. The local HD read benchmark is higher. Before I go to my IT guys with what I think is a huge potential misconfigurtion, I just wanted to get opinion from you guys.

Answer 1

MSA1500s aren't that fast by SAN standards, but misconfigured SANs often underperform on loads such as data warehouses. It is quite likely to be a mis-configured SAN (perhaps the stripe size on the array is too small). It is also possible that the SAN is inherently slow - not all SANs are created equal.

For a volume-heavy (rather than seek heavy) load like a data warehouse you will probably get much better performance from direct attach disk arrays. On a random access workload like a transactional application the limitations of the controller tend to get masked by the mechanical disk seek activity. Performance issues are much more likely to show themselves on a streaming workload like a table scan over a large fact table.

Poor disk layout such as placing log volumes on the same disk sets as busy random access workloads may also disproportionately affect disk performance on a SAN.

To get an idea of whether something is diskbound take a look at the stats for Page IO Latch waits. If these are disproportionately high then the SAN is probably the bottleneck.

Answer 2

I have an MSA1500CS and it consistently underperforms for me, at about the rates you're experiencing. The disks in mine are SATA drives. When I had them in parity raid configs (RAID5 or 6) the throughput was around 60MB/s. The controllers in there are really under powered. I've since gone to RAID10 and throughput has increased by quite a bit, largely because I'm not consuming nearly as much controller CPU resources for the parity-calcs.

Also, that device has a nasty, nasty habit of disabling the write-cache when doing certain array functions. Things like adding a drive to a disk-array, changing the stripe-size on a LUN, or recovering from a failed disk. When it does that, any parity RAID LUNs on there become very write sensitive. Throw too much write I/O, and it starts drastically slowing how fast it commits writes to the disks. Faster drives can help, but is a controller CPU resource problem for the most part.

That said, you have a SCSI drive in there not SATA. That should help performance by a good amount, though again parity RAID may not improve much. What would improve performance is using an Active/Active firmware on it, and utilizing MPIO round-robbin to spread the I/O load between both controllers. Last I checked, this could only be done in homogeneous host-OS environments (i.e. all Windows, or all Linux).

This is a device designed such that latency-sensitive workloads ("online" workloads in HP parlance) are intended to be run in a mirror set, and archive workloads ("nearline" workloads) for the parity RAIDs. Attempting to use a parity RAID in an online role is doable so long as your application is accepting of potentially very severe latency. Mine weren't, and this caused major problems.

The MSA2000 line is reportedly a lot better about this.

Answer 3

The common offenders:

• Windows disk partitions are not aligned with SAN. This is due to versions of Windows Server before 2008 starting partitions at the 64th sector.

• NTFS allocation unit size should be 64k (best practice).

• Sub-optimal SAN stripe size

You can check disk alignment with the command:

wmic partition get blocksize, startingoffset, name, index

If starting offset is 32256, partition is misaligned.

More information:

Disk Partition Alignment Best Practices for SQL Server

http://msdn.microsoft.com/en-us/library/dd758814.aspx

Answer 4

Did your benchmark tool utilize the system's queue, or ran only one SCSI request a time?

Think of transporting coal from a mine that is 50 meters from your house. You can use one truck. With SAN the mine is far away, in another city. It may be producing a lot more coal (in your case the mine is about 14 times as big), but first of all you need to put a lot of trucks into action (a queue).

Your database does normally put a lot of trucks into action, so use a corresponding benchmark tool that does so as well.

Another thing, more important: sequential vs random operation. Your database for sure needs random I/O (except the time of full backup), so I can safely guess that it will never come even near 60 MB/s performance. Because with random I/O the miners work really really slowly, so trucks and distance do not matter that much. Try to benchmark such type of workload. Compare with your local drive, and you might be surprised.

Another answer that touches this subject What is typical SAN performance?.

Answer 5

Here is a link that discusses a lot of different issues on SQL server. Go to page 19 (Appendix D) for instructions on how your IT guys should set up the SAN. It includes an article by Mr. Denny that was very helpful to me. We re-configured block sizes(stripe sizes) and number of drives per LUN on our SAN after reading and noticed about a 100-120% improvement in our environment.
Hope it helps you in some way...