RAID5+SQL Performance issues on MSA50

[Andrew_346]

Wanted to post up a problem I'm having to see if anyone else is in the same boat.

I recently purchased a DL585 with 3 MSA50 arrays to serve as a new SQL reporting DB server.

The server came with a SAS mirrored C: connected to a P600 controller in slot3. I added 2 additional P600 controllers in slot 1&2 (separate PCI-X 133MHz busses) and connected slot1 to the first two MSA50s and slot 2 to the remaining MSA50. All 3 were configured with 10-drive(72GB) RAID5, default strip (64k).

I have Win2K3 R2 x64 running, fully patched with SQL2K SP4.

While attempting to restore a 120GB database to this server, I experienced REALLY slow performance. SQL restores first create the data files needed, then populates the file with the actualy table data. It was the initial file creation and writing of zeros to the space that took the longest - nearly 3 hours. Whereas an ML530G2 with 3 MSA30s attached with 12-drive RAID5 takes only 50 minutes to restore the same DB.

I've since taken a much smaller database (2GB) and done different tests and compared to the current server and other parallel SCSI servers and different RAID levels.

For instance, the 2GB db takes 7m30s to create the data files on the 10-drive R5, but takes only 1m36s on the same array configured for R1+0. Before anyone asks, yes, the RAID5 array had finished initializing before working with it.

Also, I ran straight file copy performance with ROBOCOPY.

Copying a 2GB database backup file from a 12-drive U320 15K RAID 5 (MSA30) to another took 1m8s @ 1868MB/min. Copying from a 10-drive SAS 10K RAID5 (MSA50) to another took only 45s @ 2730MB/min.

So, by the file copy standards, the SAS drives are kicking butt. But when SQL gets ahold of it to create datafiles during a full DB restore, it sucks...

I have a ticket open with HP Support and has been escalated all the way up to the Engineering group in Houston. [insert mission control joke here].

If anyone has input, please chime in.

[Joseph Martin_1]

About the only good thing I can say about RAID 5 is that it's cheap. You will get much better performance using 1+0

[VCP2005]

You should not have performance issue on read but on write , it is still put the transaction log to logical disk on RAID 1+0.

Restore is a write activity which will min 3 times of your write/backup time... It will be alway slow compare to RAID 0, RAID 1+0.

[raadek]

Andrew,

I don't precisely know how SQL restore works. But I suspect it must be some kind of random writes in opposite to sequential writes, e.g. your test of 2GB file copy.

10K SAS should be slower than 15K U320 in random writes. RPM is a key factor in this scenario. When 'randomly' searching for a right sector on the drive you have to cope with two figures: average access time & average rotational latency. While the former is on par for both types of drives (SAS drives has smaller plates, hence it is possible with 10K RPM), the latter is 50% longer for 10K drive when comparing with 15K drive. And the size of the plate doesn't matter - you are just measuring average time to rotate your plate (i.e. half way on average).

Yes, looking at the average seek time one may think than 10K / 2.5'' could be as fast as 15K / 3.5'' - but unfortunately that is not the case in a random read / write environment.

Hope it helps.

Rgds.