SQL Server on XP 10000--What is best practice?

[Paul Gerke]

Hi All,
I have 4 array groups available on my XP 10k for use by the Microsoft group for SQL Server data. I came from the Unix admin group and am very new in the Microsoft world. In the Unix world for Oracle, SAME (stripe and mirror everything) was an accepted best practice for data stored on a SAN. I suspect that something similar is the best practice for SQL server, and that striping the data across all 16 disks is the best approach. If it is, then it would seem that the best approach for the 16 disks would be RAID5 (14+2) to get the best striping, and then rely on the cache to "buffer" while the parity is generated and the data is written. Am I on the right track?

[Hasan Atasoy]

hi paul ;


http://h71028.www7.hp.com/ERC/downloads/4AA1-6100ENW.pdf may help you.


Hasan

[Paul Gerke]

Thanks Hasan,
While that doc didn't have exactly what I was hoping for, it did get me looking in the right area to find this link: http://search.hp.com/redirect.html?type=REG&qt=SQL+Server+2005+OLTP+with+HP+StorageWorks&url=http%3A//h71028.www7.hp.com/ERC/downloads/4AA0-2948ENW.pdf%3Fjumpid%3Dreg_R1002_USEN&pos=1

It is an EVA performance test showing that striping across more disks gives better I/O.

Now if I could just figure out a way to to RAID10 across all 16 disks in the XP...does anybody know if it can be done?

[IBaltay · Accepted solution]

hi,
1. its a good idea to avoid the 3D+1P, which could be the bottleneck for the SQL random read. In XP for the RAID1 there is only 4D+4D configuration of the array group (8 physical disks).
2. But there is no need for the RAID1 4D+4D, because there are other RAID5 concatenation configs like 14D+2P (16 physical disks in the array group) or the max 28D+4P (32 physical disks in the array group), which can be used for the SQL.
3. and there is a option called LUSE (which is not stripped but spanned), but allows you to create and collect the disks from the different array groups as a smaller chunks

[IBaltay]

but if you insist on the RAID1, which max config is 4D+4D, you can create 2 array group of 4D+4D and then create the LUSE volume made of the first and second RAID1 array group. Thus you have 16 physical disks in the game of RAID1.

But as I have said the better solution here is to use the RAID5 concatenation with interleaving (14D+2P and /or 28D+4P) or buying a new XP24000 and apply the ThP solution which is implemented only in the XP20/24k firmware :-).

[Paul Gerke]

Thanks IBaltay,
I appreciate the information. That is what I had suspected. I had been recommending 14+2 bet was getting pushback from management.

[Nigel Poulton]

Just my penny worth. As we know, performance can be so subjective. It really depends on your anticipated I/O profile.

Most XP configurations I see these days use RAID5 (7+1), RAID5 Interleaving (this is what IBalty refers to when he mentions the 14+2) or they use ThP. This is because the XP is a good storage array and can usually get away without RAID1.

However, there are situations where RAID1 (the XP does RAID10 which is better) vastly outperforms RAID5. I mentioned in a previous post that if the XP cannot coalesce random writes into full stripe writes then the penalty incurred for a RAID5 write is 4 back end operations per write â
1. read of old data
2. read of old parity
3. write of new data
4. calculation and write of new parity.
So every random write causes 4 back end disk operations plus a calculation for the DRR chips on the back end directors.

This is a huge penalty if your I/O profile is a LOT of small block random I/O.

If you cant get away with RAID5 you can do the following â

The only way to use all 16 disks for RAID 10 is to create 2 x RAID10 (4+4) Array Groups. The XP does RAID10 which is 1+0 as follows â
1 = mirroring. First it creates a 4 pairs of mirrored drives
0 = striping. Then it stripes data evenly over the 4 mirrored pairs

So I suppose this sort of gives you mirroring and striping according to your SAME.

However, most implementations and interpretations of SAME are as follows â
Create RAID10 volumes on your storage arrays and present these volumes to your host. Take these volumes on your host and use your hosts volume manager to create them into a stripe set (RAID0 with no parity, just pure striping). This way you are striping over mirrored volumes. This is your typical SAME.

However, you might want to have your data on RAID10 and your logs on RAID5. As you will know logs generate sequential writes which perform better on RAID5 as more spindles are used (less are wasted on mirroring/parity).

Like I said, very subjective ;-)

[Amar_Joshi]

There are several white papers which claims that RAID5 can outperform RAID1 for the READ IOs but I haven't see it much in case of MS-SQL and XP environment. With my experience I will recommend you to stay with 4D+4D (RAID10) and if possible, multiple 4D+4D. AND NOT TO LUSE THEM. LUSE will decrease your performance or in other words Open-V has better performance. I would like to see a Volume Manager to build a logical volume on the host (spanning volume across multiple 4D+4D).

Volume Manager will save you from other potential problems of CHA-MP being bottleneck to IOs. I have seen that less than 3000 IOPS coming from MSSQL can load the CHA-MPs to 70% or above and in that case if you don't have multiple volumes presented on different CHA-MPs you will be handicapped without Volume manager.

Last point I have here to highlight is that, plan and distribute your load across multiple HBAs and if you can avoid HBA load balancing (RR or SQST or SP) and just use preferred controller setting (balanced across available HBAs) you will be just fine to run the setup longer without hick-ups.

My 2 cents worth.

[Paul Gerke]

Thanks mackem,
Your response covered what I had been thinking. And after a meeting with my manager and the SQL DBA, we implemented 14+2 RAID5. Hopefully the XP's performance will protect us from the parity penalty.

Thanks Amardeep,
I especially appreciate your response for recommending mirroring. (It is always good to get differing points of view!) If the current 14+2 implementation doesn't perform well I will be sheepishly implementing your recommendation. I hadn't thought about doing OS level striping to merge two 4+4 LUNs since I hadn't been told that it could be done on Windows. I used to do it all the time with LVM. Now I have more to talk about with the other Microsoft Admins.

Thanks again to both of you,
Paul

[PP BIJU KRISHNAN]

Hi,

As amardeep mentioned please use multiple HBA's and dedicated CHA ports for this server.

I have seen windows hosts pumping 3K to 4K IOPS on one LUN causing high CHA utilization and high cache write pending.