Which SQL files benefit the most from RAID 10 vs RAID 5?

Question

Assuming I can't put everything on RAID10, which parts of SQL (Data files, log files, TempDB, Backups, etc) benefit the most from being on RAID 10 instead of RAID5?

Does it depend on the type of database (OLTP vs Reporting vs Analysis)?

What kind of actual performance increase should I see going from RAID5 to RAID10? 10%? 50%? 200%??

Technical details: New HP XP24000 disk array, using 300GB 15k drives

We have to make some decisions up front about how much will be provisioned as RAID5 (28x4) vs RAID10 (4+4). This is a one-time provisioning, and can't be changed later, so we're trying to find the right balance of cost/GB vs performance benefit.

Solution 1

OLTP - RAID10 Logs, Data RAID5
OLAP - RAID10 Data

Read speed is more critical in OLAP, in OLTP you generally want log write speed.

Solution 2

As other posters have pointed out, it varies with OLTP/OLAP workloads.

Kendal Van Dyke has a series on RAID levels with SQL Server with the data to back it up. His tests weren't done with HP SAN disks, but you get the idea.

For a RAID 5 array using the same number of physical disks as a RAID 10 array, RAID 5 offers 10-15% better performance for 8 KB random reads but a whopping 60-65% worse performance for 8 KB random writes

Be sure to check part 5- RAID 10 vs RAID 5.

Solution 3

Log file - there write speed is critical. Db is read spead limited.

Solution 4

Not sure if the XP operates like the EVA series, but if so, the vRAID level is more about data redundancy than performance. Your performance comes from how many disks are in your disk group, and their spindle speed. As HP storage engineers say: "need more performance with an EVA?, add more disks; need more capacity with an EVA?, add more disks". You might consider a smaller / lower-spec disk group to hold your tempdb vDisks.

Solution 5

T-Logs and tempdb. Generally, I like tempdb to be on the fastest storage I have. T-Logs you want the best write performance. For the rest RAID5 should be fine.

View more solutions

Comments

[Simon Catlin]

BTW, you're a lucky bu**er to have an XP to play with.

[BradC]

I'm not the SAN admin, but I've been told the XP and EVA work somewhat differently, the EVA puts a virtual "layer" of RAID over the grouped underlying disks (or some technical foo like that).

[TomTom]

Even OLTP - sorry - Data on Raid 5. Data is NOT written during changes, ONLY (!) the log. Data is written in a background thread. As long as the write speed is fast enough, log write speed is highly critical for writes and db read speed for queries and updates (to find the pages to change). The changes are NOT written to the data file upon commit. This happens later (purging, checkpoint).

[phoebus]

That's why I said Data Raid 5, Logs Raid 10.

[Simon Catlin]

Ah - different beast then.