July 8, 2001 at 12:01 pm
Hi all
Recently I underwent some hair pulling planning over the disk-subsystem for our new production web-apps coming out soon. One of the big problems I have found is trying to architect a solution where I have 2 or 3 OLTP based applications (200 to 500 users each at times) all sharing the same "disk farm" (for one of a better word). There are many papers that discuss the seperation of the log files to their own disks due to their serial nature etc, and a variety of papers confirming then discrediting the use of raid-5.
Anyhow, the problem I had was the fact that 3 reasonably busy OLTP applications will share 8x18gig 15k drives. Configuring and planning file-group layout is not on difficult but even harder to manage performance wise. With no more money$ for more disks (yet), its a tough one. Perhaps the 18gig is a bad choice and I should look at 16x9gig 10 or 15k drives instead.
Any thoughts on similar senarios?
Cheers
Chris
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
July 8, 2001 at 10:58 pm
What type of RAID configuration do you have now. I generally use the following format:
Drive C - Binaries
Drive D - Primary Data Files - RAID 5
Drive E - Secondary Data Files - RAID 5
Drive F - Log and Backup files - RAID 1
Drive G - Windows pagefiles.sys file - RAID 1
Believe it or not, you can squeeze a little performance boost by spreading your data files over multiple drives. Even in a RAID 5 env. If the drives have different drive letters, then SQL Server will create parallel asynch. I/O. There's some white paper at MS to support this as well for backups. If you can afford another RAID controller, that will speed you up more than additional drives though. Always 1 million ways to do a single task though!
Brian Knight
Brian Knight
Free SQL Server Training Webinars
July 10, 2001 at 9:06 am
Brian
Thanks for the reply. Interesting methodology for the file placement. Id be interested to hear your comments on RAID-5 in such an environment.. namely a marked increase in writes. In a 16 drive solution i probably would have gone for a single raid-10 giving..
Drive C - Binaries
Drive D - Primary Data Files - RAID 0+1
Drive E - Log and Backup files - RAID 1
Drive F - Windows pagefiles.sys file - RAID 1
As mentioned this is all fine with a single application database, but throw in 3 other applications with their own log files and auditing requirements and it becomes difficult to plan for the possible performance overheads.
As a side note, I read that measuring write queue lengths on the disk subsystem where log files reside is irrelevent; due to the fact that the log writer uses
a queue length of 1. So is this saying that SQLServer does not use the OS's low level disk queuing model (which I believe all requests would go through) and does direct writes?
Cheers
Chris
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
July 10, 2001 at 9:18 am
Most of my DBs are using reads. If you're doing a large amount of writes as you are, I can't argue with your choice of RAID. I would much prefer to use RAID 1 myself but due to the read factor and cost, we had to choose the implementation we did. The only thing I may change in your implementation is to create additional files on other drives letters.
Brian Knight
Brian Knight
Free SQL Server Training Webinars
July 10, 2001 at 11:04 am
Im not sure I agree with a separate drive for the pagefile. I know it's recommended, but if you're even close to properly configured, you shouldnt be hitting the page file very often. We keep ours on the same disk with the OS - that it's cheaper that way helps too!
Andy
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply