10 physical disks available - best configuration for data/log/backup?

Question

10 physical disks available - best configuration for data/log/backup?

John Davys

Valued Member

Points: 56
More actions
January 29, 2013 at 9:47 pm

#269614

I have 10 300GB SAS 15k rpm 6Gb disks available to me on a HP P2000 SAN. I have a 150GB database and I want to configure these 10 disks for best performance. There are around 2 billion rows in one particular table and the workload consists mainly of querying out of this table and loading/deleting data into/from this table. Using SQL Server 2010 Standard Edition.
The OS for the server is on separate local disk, same type of drive, one 300GB disk mirrored. SQL Server tempdb is also on this C: drive.
So given these constraints, this is what I think would be best, but I would like to know what others think and why:
one array of 4 disks RAID10 for database files i.e. 600GB available space
one array of 4 disks RAID10 for log files, 600GB available space
one array of 2 disks RAID1 for backup, 300GB available space
The loading and deleting hit the database and log file hard which is why I have configured the log drive to have 2 disk spindles. I can live with slower backups.

Viewing 9 posts - 1 through 8 (of 8 total)

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

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 1

If each disk is 300GB, how is it that you're only getting 600GB out of 4 drives and 300GB out of 2 drives according to your description? Is that for mirroring?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

John Davys Valued Member Points: 56 More actions · Answer 2

Yes that's correct. This is a production system and some form of redundancy is required.

I was just wondering if instead of the layout I suggested, would it be better to just have one array of 10 disks in RAID5 i.e. 9 available disks, capacity=2.7TB and just have everything on the one volume (or split it via Windows into logical drives) - is the advantage of more spindles better than separating database & logs & backups into their own arrays?

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 3

What about TempDB, an archive, and a spare? 10 Drives doesn't seem to be enough even if TempDB is on DAS (which would mess with clustering if you went that way).

To answer your question (apologies, I ask another question), what are you shooting for with your SAN? Max capacity with the ability to rebuild bad drives in an online fashion? Max speed (which can usually be done MUCH better by writing good code)? Max safety? or ???

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

John Davys Valued Member Points: 56 More actions · Answer 4

The SAN has other disks and also spares etc. The 10 disks I am talking about are available for dedicated use for this sql server.

I would certainly like to have more disks available, but given the constraints the question really was whether anyone had experience of whether SQL Server performed better with disk arrays dedicated for purpose i.e. separate database, logs & backup disk arrays where each disk array has few spindles (e.g. 2 in my case) versus a disk array with more spindles (e.g. 9 in my case) but with all the disk activity going to the same array. So I'm looking just at performance in this case as with either configuration I have redundancy to cope with a failed drive.

I may be able to test this myself soon when I reconfigure my Test environment.

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 5

To answer that question, my experience has been that the more spindles you can involve, the better the performance. A lot of SAN folks will argue against that but I'm not sure why because it actually does make a lot of sense.

To wit, I normally try to separate MDF and LDF files to their own physical sets of spindles as well as setting up TempDB on it's own set of spindles. If I can, I'll set it up so that the MDF/NDF files of TempDB are on separate spindles from the LDF files but, no matter what, I try to put TempDB on it's own drive(s) so I can configure it differently than all of the others.

Of course and as a sidebar, that's not always possible with the ridiculously sized hard disks they have now. It was so much easier to right-size and get more spindles involved when disk size was much smaller. For example, I just can't see dedicating a 300GB drive to the LDF files of a system that won't grow to more than 600GB across multiple databases. It was a little tougher on electricity and cooling but it even allowed for faster disk replacement if one went bad because the system didn't have to rebuild so much.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

John Davys Valued Member Points: 56 More actions · Answer 6

Thanks for your replies Jeff. I think you are basically agreeing then with my original disk layout. I agree with your comment about disk sizes being annoyingly large now - I am old enough to remember setting up servers with arrays of 9/18/36/72GB disks and performance was pretty good even though disk technology has improved since then.

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 7

The original layout isn't bad although I'd be tempted to NOT mirror backup drives and I'm pretty sure you won't need 600GB of LDF space for a 600GB set of MDFs. I'd be might tempted to use at least one of the drives as a dedicated drive for TempDB.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

John Davys Valued Member Points: 56 More actions · Answer 8

Thanks - I think you're right on both suggestions. The backup is written to tape anyway so I don't need to protect that disk from a failure and yes it would probably be better to move the tempdb area onto the SAN than use local disk. Certainly don't need the whole 600GB for logs.