I've been reading about 10k drives being able to run 166 transactions per second vs 250/sec on 15k drives, which in both cases is just the number of rotations per second. What does this really mean for my DB though? If I buy a 15k drive, can I expect to write 250 rows to a table per second, or is it just the theoretical limit due to the atomic nature of an ACID database?

The raid part (from the title) is: if I have a series of 10k drives (e.g. 4 of them) in a raid 10, can I expect to get 2 times the amount of transactions per second (assuming I have nonvolatile cache on the raid controller)? Or, does the number stay exactly the same?

One final thing, how much theoretical gain in trans/sec will I have (when a raid 10), if I move from 10k drives to 15k drives?

  • 2
    Hard drive IOps != database transactions. – EEAA Feb 22 '12 at 1:25

This depends on whether your system is bottlenecked on IO under your typical workload. It's not a straightforward translation.

Theoretically, if your typical database write workload were completely IO bound, and if your IO subsystem was twice as fast and you were still completely IO bound, then you could achieve a 2x increase in write performance.

In reality, you need to consider many factors:

  • Do you have enough RAM to hold your tables in memory and to process memory intensive queries? If not, you'll have to mix disk reads and writes, harming performance.
  • Is your RAM and CPU fast enough to saturate your IO for writing? Remember you're sharing these resources with other queries, or even complex foreign key constraints that cause a lot of validation.

Once you get a handle on the existing bottlenecks, you can move onto other ideas for eliminating them.

  • Is transaction logging using more IO than it's worth?
  • Are you using battery backed raid controllers to improve the performance of acid commits to disk?

When addressing database performance issues, you should identify your bottlenecks and work to eliminate them in the order of largest to smallest.

  • thanks. Btw, we'll have non-volatile cache on the raid rather than battery backed. This isn't a problem is it? – orokusaki Feb 22 '12 at 2:17
  • 1
    All controllers worthy of the name have NVRAM that caches writes to improve performance. The point of the battery (or solid state capacitors) is to supply the NVRAM with electricity when the server itself is powered off, thus preserving the NVRAM's contents. This way the cached writes that didn't make it to disk yet don't get lost, even in the event of a power failure. I would find it strange if you had a controller that has NVRAM but no battery to back it up. – daff Feb 22 '12 at 2:54

Compared to what? What is your basis for comparison? In general, yes... faster and MOAR disks are better! 10k versus 15k is kind of a wash in some situations, but depends a bit on whether you're using 2.5" or 3.5" disks. These days, solid-state drives (SSDs) are also something to consider in new deployments.

But honestly, this is something that probably needs to be tested with your particular workload...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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