Proper splitting of workload among disks

Question

Proper splitting of workload among disks

bobmclaren

Old Hand

Points: 319
More actions
March 16, 2010 at 4:13 pm

#386523

I have been provided a server that is maxxed out at 6 disks. When deciding how to arrange the disks, which is the lesser of two evils?
A) Two LUNs, slow performance -- C: drive is mirrored (2 disks), D: drive is RAID5 (4 disks). OS and Log files on C:, data on D:
B) One LUN, faster performance -- C: drive RAID5 (6 disks) with OS, log files and data together.
I ask this because I chose option A long ago, and I have since read several articles (and even a paragraph in my SQL 2005 admin book) stating that a 4 disk RAID5 array is an atrocity and should never be used in a production environment.
Since my disk IO performance on the D: drive is sub-par, I am tempted to gut the box and go for option B. But then I would be committing the ultimate sin of putting my log files on the same disk as my data files.
Thoughts?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Paul White SSC Guru Points: 150467 More actions · Answer 1

bobmclaren (3/16/2010)
I have been provided a server that is maxxed out at 6 disks. When deciding how to arrange the disks, which is the lesser of two evils?
A) Two LUNs, slow performance -- C: drive is mirrored (2 disks), D: drive is RAID5 (4 disks). OS and Log files on C:, data on D:
B) One LUN, faster performance -- C: drive RAID5 (6 disks) with OS, log files and data together.
I ask this because I chose option A long ago, and I have since read several articles (and even a paragraph in my SQL 2005 admin book) stating that a 4 disk RAID5 array is an atrocity and should never be used in a production environment.
Since my disk IO performance on the D: drive is sub-par, I am tempted to gut the box and go for option B. But then I would be committing the ultimate sin of putting my log files on the same disk as my data files.

Never put log files with anything else. Fast log flushes are absolutely critical to SQL Server. Log I/O is predominantly sequential in nature, and therefore is best placed on RAID 1. With six disks, my guess would be:

LUN 0: 2 disks RAID 1 for the OS.

LUN 1: 2 disks RAID 1 for the log.

LUN 2: 2 disks RAID 1 for everything else.

or

LUN 0: 2 disk RAID 1 for the log

LUN 1: 4 disk RAID 5 for everything else.

Possibly the best you can do within the limits you have - not too many options with 2 disk LUNs in particular. Ideally, you would have a dedicated LUN for tempdb too. There is nothing wrong with RAID 5/6.

So much depends on information not yet provided though: the purpose of the database server, reliability and performance requirements, the size of the disks, number of controllers, and so on, and so on.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

bobmclaren Old Hand Points: 319 More actions · Answer 2

Thanks for the detailed response Paul. While we're at it, let me drop another riddle out there.

Assuming you are on a 4 core server, what would you chose as a high performance disk configuration for your data files?

A) All your data files on an 8-disk RAID 10 array.

B) Splitting your data files into filegroups, and placing them on 4 RAID 1 arrays.

Based on the reading I have been doing, I'm inclined to think that option B would be the most performant, as it takes better advantage of parallellism because each CPU is performing IO on each file simultaneously.

I am wondering though, how those benefits would stack up against RAID 10.

Paul White SSC Guru Points: 150467 More actions · Answer 3

bobmclaren (3/17/2010)
Thanks for the detailed response Paul. While we're at it, let me drop another riddle out there.
Assuming you are on a 4 core server, what would you chose as a high performance disk configuration for your data files?
A) All your data files on an 8-disk RAID 10 array.
B) Splitting your data files into filegroups, and placing them on 4 RAID 1 arrays.
Based on the reading I have been doing, I'm inclined to think that option B would be the most performant, as it takes better advantage of parallellism because each CPU is performing IO on each file simultaneously.
I am wondering though, how those benefits would stack up against RAID 10.

The 'thread per file[group] thing is a myth I am afraid 😉

See http://blogs.msdn.com/psssql/archive/2007/02/21/sql-server-urban-legends-discussed.aspx (Microsoft CSS Team)

The RAID-10 array, by quite some margin. It is difficult to distribute data over file groups to produce anything like optimal results. The RAID-10 array makes that easy, and puts the storage intelligence where it should be - at the hardware level.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

bobmclaren Old Hand Points: 319 More actions · Answer 4

Wow. It seems I learn something new everyday, then un-learn it the next day...

Thanks again for offering your insight.

Paul White SSC Guru Points: 150467 More actions · Answer 5

bobmclaren (3/17/2010)
Wow. It seems I learn something new everyday, then un-learn it the next day...Thanks again for offering your insight.

So true isn't it? If I learn one new thing a day, without having to unlearn anything, I'll have SQL Server covered in three or four hundred years. The core engine anyway...;-);-)

And you are welcome, of course.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi