RAID10 vs. RAID5 for data files

Question

We are trying to evaluate the potential performance improvements that could be gained by switching an existing applications .mdf files to a RAID10 LUN vs. the existing RAID5 LUN they are living on right now. The transaction log already lives on a RAID10 LUN.

So the question is, if we move data files to RAID10 would the end user see performance improvements in the GUI? or would it be just faster writes to the data files when a checkpoint occurs and the changes get written to the data file but no improvement would be seen at the end user level?

Answer 1

There are two types of activity to consider here: reads and writes.

As you correctly point out, MDF/NDF writes are done through the checkpoint process, and users shouldn't have their transaction time affected.

Reads happen when the data that is needed is not yet in RAM (in the buffer cache). So ideally, user activity shouldn't be affected often. But when it is, the data can be fetched quite quickly whether RAID 10 or 5. The pain point for RAID 5 is with writes not reads.

But if you lose a disk - what then?

With RAID 10 there's no loss of performance. When a replacement disk is put in, the mirror is copied over it. With RAID 5, every read requires some maths to be done. And when a new disk comes, it has to read a lot and write a lot.

But RAID 5 is cheaper. 1TB of disk will get you 800GB of space (depending on your setup), but RAID 10 will only get you 500GB. Disk activity is slightly more expensive though.

It's "fine" to put data files (not logs) on RAID 5. But if disk is cheap enough, RAID 10 is better.

Answer 2

It depends a little on what storage system you have behind the scenes. You see, read and write IO operations are very different.

On a RAID 5 to perform a single block write you must:

• Read the update block.
• Read the parity block.
• Write the new block.
• Write the new parity block.

So for a single random write, RAID 5 needs 4 operations per write. This is called 'write penalty'.

RAID10 you need to simply write the new block twice - so it's write penalty is 2.

At a simplistic level for random write operations - RAID5 is much worse. However, once you factor in caching, this picture changes. Write IO is under a soft time constraint - as long as the total number of IOs can complete in a reasonable timeframe, you can write cache. If you built a full stripe, you can write it all in one go - and you don't need to read back to compute parity.

That can make 4+1 RAID5 have a write penalty of a mere 1.25, which means it's better than RAID1+0 for sustained sequential write IO. Which may well be exactly what your database is doing. (Transaction logs in particular are basically exactly this. Your data files may not be, it depends a bit on whether you're updating or adding data)

For read IO you have a slightly different situation - read IOs are under a hard time constraint - it's not complete until the data has been fetched. Your storage controller will have a cache and prefetch mechanism, but ... well, I assume your DB is caching into memory anyway.

Practically speaking this means your read IO from disk (for a database) is likely to be near worst case from that perspective - you can't prefetch true random IO. RAID 1+0 will therefore be more benefical there - because there's two possible sources of any given read (each of the submirrors).

In terms of failure mode - RAID 1+0 is pretty simple. If one drive fails, it's simply a copy from the other submirror. RAID-5 reconstruction requires - basically - reading the whole RAID group. So 4 drives (in a 4+1) instead of 1. And during this time, performance will be significantly degraded because any read must reconstruct - it needs a read from all 4 drives (and the drive are also busy). Failure frequency isn't that high on enterprise class drives, but it's still going to occur from time to time.

And lastly - by no means least - is price for drive and the quantity you need. Datacentre space isn't cheap, and neither are enterprise class drives. RAID 1+0 'costs' you 50% of your disk. RAID-5 costs 20%. (I probably wouldn't suggest more than 5 disk raid groups - the bigger, the higher the failure probability and the longer the rebuild time).

But yes - you've got to consider the caching element when comparing RAID types, because the simplistic drive speed access times are massively improved by efficient caching. All controllers do caching, and the really expensive storage arrays have vast amounts of it. So this may be more relevant if you're SAN attaching.

It may also be worth considering SSD - SSD is expensive per gig, but has a good cost-per-IOP. And for the random read profile that's so hard for spinning rust to handle, it's nearly perfect. Hybrid drive or tiered storage options may also be a factor there.