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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy