SQL Server Performance Forum – Threads Archive
Raid types- Correct Placement of Data and log file
The question is in regards to configuration of raid types, I am currently limited to 5 drives in my subsystem, I have the space to fit an extra spindle making it 6 in total, but that would be on a cost of loosing a hot spare. The trouble I am having is to decide, A) Shall I make a 5-disk Raid 5 and place my Data and log files on the same array.Or
B) Shall I make 2-disk Raid 1 (For Logs) and 3 disk Raid 5 (For data) With Config A, The more spindles i have in an array, the faster disk I/O will be.
With Config B, I will prevent disk I/O contention issues by segregating Logs and data into 2 different arrays. My goal is to achieve good Read performance, Currently our database is running on a raid 1 with 2 drives, disk Q lengths are up the walls. They are twice as high as they should be. I would prefer to go ahead with 5 drive raid 5, perhaps doubling I/O per sec as to what I currently have. But then I worry about I/O contention ! Just as a note : if some one is wondering if high disk q lengths might be due to some other issues, and not heavy I/O ,we have investigated some memory counters including the Process/Working set, Buffer manager, Page life expectancy for apparent memory issues, Cpu usage is well under 40 %, hyper threading is tested as well and network teaming has kept network usage well under 10 %., Execution plans show no sings of outdated stats nor heavy physical reads. Specifications :
Drives 15k
availability agreement : 1 hour down time window, every 8 weeks
Backup : Mirroring (2005). Any help would be greatly appreciated
For DB’s (and this not on for SQL), there is 1 golden rule: NEVER USE RAID5. For reading you might gain some speed, but remember that most read come (or should come) from memory. Buffer hits should be over 95% (except if you run some special routines that read the whole dB) otherwise you need more memory.
RAID5 may be good for reading, but it is hell for writing.
I would put an extra disk and make a RAID1 (2 disks) for the transactionlog+system files and a RAID10 (4 disks) for the DB.
IF Debugging = removing bugs from program THEN programming := putting bugs in program;
I agree with kriki, do not use Raid5. Raid5 requires 4x as many i/o operations for each write, and 2x for each random read (which any db will do a lot of). So read and[/i[ write performance suffers with Raid5. However, with this limited I suggest different layout. I suggest 3 Raid1 arrays: 1 for system files and tempdb, 1 for data files, 1 for log files. You always want to separate data and logs on different drives (like kriki suggested), but it is also good performance practice to separate the tempdb database also. My preference is that takes greater precedence than having the Raid10 for data. Then, have a 7th drive for your cold spare, and monitor your event logs for drive errors. Hope this helps
Rather put the log and os/apps on the same pair if you have this limited subsystem, and dedicate more spindles to data — ie. one pair mirrored for both os/apps and log, the rest raid 10 (if you can get enough capacity). I don’t agree with kriki — Raid 5 isn’t *that* bad; there’s a write performance hit, but not really a read performance hit, so on systems that are not write intensive, it’s probably OK. It’s not OK for high volumes of writes.
Just for the curious…http://www.baarf.com —
Frank Kalis
Microsoft SQL Server MVP
Contributing Editor, Writer & Forum Moderatorhttp://www.sql-server-performance.com
Webmaster:http://www.insidesql.de
Why is Microsoft suggesting the use of RAID 5?
http://msdn2.microsoft.com/en-us/library/ms190764.aspx
RAID 5 Also known as striping with parity, this level is the most popular strategy for new designs. RAID 5 is similar to RAID 4, because this level stripes the data in large blocks across the disks in an array. However, RAID 5 differs because it writes the parity across all the disks. Data redundancy is provided by the parity information. The data and parity information are arranged on the disk array so that the two types of information are always on different disks. Striping with parity offers better performance than disk mirroring (RAID 1). However, when a stripe member is missing, read performance is decreased, for example, when a disk fails.
From the same page:
RAID 1 level is also known as disk mirroring because it uses a disk file system called a mirror set. Disk mirroring provides a redundant, identical copy of a selected disk. All data written to the primary disk is written to the mirror disk. RAID 1 provides fault tolerance and generally improves read performance but may degrade write performance.
Wow. That MS advice is just so wrong.
That may be specific to the set of environment and not to the universal, how about your environment & setup in this case/
]]>