Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

The average Input/Output Operations per Second a disk can sustain is a very important factor in the tuning of MySQL / InnoDB databases, as it drives the speed with which Dirty InnoDB Buffer Pool pages are flushed back to the disk.

Is there a method or a formula I can use to estimate the average I/O Operations per Second my RAID 10 disk array can run on?

share|improve this question
add comment

2 Answers

tl;dr Yes you can do a rough and ready calculation but benchmarking is advisable if you need accuracy. There are many factors beyond raw disk performance that can influence IOPs, in particular the RAID controller.

If you have the technical specifications for the disk, the quick calculation to determine IOPs is: 1 / (average latency + average seek time) = random IOPs.

The contributory factors:

  • Average latency is the time taken for the disk sector requested to rotate under the drive head.
  • Average seek time is how long it takes the drive head to locate itself over the disk track requested.
  • Rotational speed (quoted in revolutions per minute) isn't used directly in the calculation but affects the above. It is also the headline figure quoted in any drive specification.

While there is variation across drive models, you can apply a rough and ready figure to each of the common rotational speeds.

  • 7200 RPM ~75 IOPs
  • 10K RPM ~125 IOPs
  • 15K RPM ~175 IOPs

With the majority of enterprise class RAID controllers, your read performance for common RAID levels (1/10/5/6) will broadly be in line with IOPs * number of disks. Cheaper controllers may impose a penalty. Write performance however is significantly effected by the chosen level:

  • RAID 1 & 10 = IOPs / 2
  • RAID 5 = IOPs / 4
  • RAID 6 = IOPs / 6

For example, a RAID 10 array of 10 15k disks equates to:

  • (175 * 10) = 1750 IOPs read
  • (175 * 10) / 2 = 875 IOPs write

Of course, most workloads are a mix of read and write so its necessary to consider the r/w ratio to determine the capacity needed.

share|improve this answer
add comment

Use this calculator: Disk IOPs calculator

of course YMMV due to controller, caching, IO size, etc. 16KiB for data and 128KiB for log is a good estimate.

share|improve this answer
2  
What in the name of all that is Codd is "16KiB for data" supposedly good for? –  Mark Storey-Smith Mar 1 '13 at 1:01
    
I'm sorry, but it's exactly because I cannot afford working with guesses that I've posted the question. –  LMC Mar 1 '13 at 13:22
1  
If you don't understand how data access patterns are different for data vs log files, you are in way over your head. If you don't know KiB vs Kb, you are in over your head. If you ask how to calculate IOPs for a particular combination of disks, and can't use a calculator that does just that, you are in over your head. –  AceCTO Mar 2 '13 at 15:57
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.