Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Proper splitting of workload among disks Expand / Collapse
Author
Message
Posted Tuesday, March 16, 2010 4:13 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, August 02, 2012 9:19 AM
Points: 25, Visits: 128
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?
Post #884293
Posted Wednesday, March 17, 2010 6:23 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 5:59 AM
Points: 11,168, Visits: 10,931
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #884585
Posted Wednesday, March 17, 2010 7:55 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, August 02, 2012 9:19 AM
Points: 25, Visits: 128
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.
Post #884670
Posted Wednesday, March 17, 2010 8:18 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 5:59 AM
Points: 11,168, Visits: 10,931
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #884698
Posted Wednesday, March 17, 2010 9:17 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, August 02, 2012 9:19 AM
Points: 25, Visits: 128
Wow. It seems I learn something new everyday, then un-learn it the next day...

Thanks again for offering your insight.
Post #884795
Posted Wednesday, March 17, 2010 9:46 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 5:59 AM
Points: 11,168, Visits: 10,931
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #884826
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse