SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Proper splitting of workload among disks


Proper splitting of workload among disks

Author
Message
bobmclaren
bobmclaren
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 135
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?
Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35588 Visits: 11361
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
SQLblog.com
@SQL_Kiwi
bobmclaren
bobmclaren
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 135
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
Paul White
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35588 Visits: 11361
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
SQLblog.com
@SQL_Kiwi
bobmclaren
bobmclaren
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 135
Wow. It seems I learn something new everyday, then un-learn it the next day...

Thanks again for offering your insight.
Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35588 Visits: 11361
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
SQLblog.com
@SQL_Kiwi
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search