It would be very helpful to have such statistics. Usually, I try to estimate number of IOPS an application may require to run smoothly and to be responsive. With such information, I can then perform a kind of capacity planning to evaluate which RAID level is optimal to use and to estimate number of spindles/disks to achieve required IOPS (as each disk can provide a certain amount of IOPS based on its rotational speed, average latency and average seek time).
Just an illustrative example. According this document, MS SQL Server 2008 generates 29000 IOPS in 3557 SQL transactions (per second). Let's suppose we have a database with such IOPS required where 75% of requests are READ IOPS and 25% are WRITE IOPS (1 READ request or 1 WRITE request is 1 IOPS). This example won't count with cache hits.
Now, how many IOPS will have to provide underlying disk array/disks? We can use this simple formula to estimate it
TOTAL_IOPS = REQ_IOPS x READ_IOPS + REQ_IOPS x WRITE_IOPS x RAID_PENALTY
In our example, we have
REQ_IOPS = 29000,
READ_IOPS = 0.75 and
WRITE_IOPS = 0.25. The only missing variable is so-called RAID penalty. A single SAS disk (of 15k RPMs) has around 200 IOPS in average. But when a disk is part of a RAID group we can’t get this amount of IOPS because some sort of calculation needs to be done to write data to the disk so that we can recover from a disk failure. So RAID1/10 (with 2 sub-mirrors) has penalty of 2 because there will be 2 writes to take place, one write to each of the disks while RAID5/50 has penalty of 4 because for each change to the disk, we are reading the data, reading the parity and then writing the data and writing the parity before the operation is complete. RAID6 has then penalty of 6.
What does it mean for us? Lets' have RAID10 configuration with
RAID_PENALTY = 2
TOTAL_IOPS = 29000 x 0.75 +29000 x 0.25 x 2 = 36250 IOPS
TOTAL_DISKS = 36250 / 200 ~ 182 disks
In RAID5 configuration with
RAID_PENALTY = 4
TOTAL_IOPS = 29000 x 0.75 +29000 x 0.25 x 4 = 50750 IOPS
TOTAL_DISKS = 50750 / 200 ~ 254 disks
The above example is showing us that achieving 29000 IOPS obviously requires more disks in RAID5 configuration than in RAID10. Another question is what is available disk capacity of such configurations? If our SAS disk has 500GB then in RAID10 configuration we will have 500 x 182 / 2 = 45.5TB while with RAID5 500 x (254 - 1) = 126.5TB (the same configuration with RAID10 would require 506 disks!!!). Pretty nice.
So do you need IOPS at the best price? Then go with RAID10 as it has the best price/IOPS ratio. Or do you prefer higher capacity but with similar IOPS? Then go with RAID5 as it has the best price/MB ratio.
Finally, this table summarizes common characteristics of various RAID levels and may help you to choose the right one:
As you can see RAID10 is the most suitable one from performance perspective. RAID5 doesn't perform well in writing as it has penalty of 4 but it provides better capacity.