Expected IOPS for SQL server

[benander]

We're running 3 SQL servers with SAN and have been experiencing more IOPS than our SAN can handle. For instance, one SAN has 4 disks with 15k RPM drives, so max it can handle approximately 720 IOPS but we're experience spikes of up to 1350.

My understanding is that you want to keep the number of IOPS to about 75% of the max your SAN can handle. If this is the case and our average were around 900 IOPS then we would want our SAN to handle 1200 IOPS at max. Am I correct in this assumption, or is there a difference approach I should be taking?

-Ben

 

[xmsre]

Your RAID type and the read/write mix have a lot to do with it as well.


For RAID 5:
read performance = P*(N-1)
write performance = P*(N-1)/4

for RAID 10:
read perfprmance = P*N
Write performance = P*N/2

Where P is the performance in IOPS of a single spindle, and N is the number of spindles in the array.

As the percentage of writes in the workload increases, the performance of RAID 5 drops much faster than RAID 10. The industry rule of thumb is: If the write penalty of your chosen RAID type is higher than the read/write ration of your application workload, then your chosen RAID type is inappropriate for the application workload.


If you assume a 1:1 read/write ratio, then those 4 spindles in RAID 5 give you 337 IOPS. Those same 4 spindles in RAID 10 give you 540 IOPS.

Even with an overly rosey assumption of a 4:1 read/write ratio, those 4 spindles in RAID 5 would only give you 459 IOPS. The same 4 spindles in RAID 10 with a 4:1 r/w ratio gives you 648 IOPS. I don't know where you're getting the 720 from.