I/O Separation
-
I've encountered a few schools of thought about I/O separation:
1) Don't bother. If you have X disks, do a RAID5 with all x, make sure you have decent read/write cache on the controller and let the controller work it all out. Separating OS & binaries & paging file, transaction logs (sometimes even separting multiple sets of transaction logs), tempdb, Data (heaps and clustered indexes), non-clustered indexes, and sometimes even multiple levels of data and NCI's via filegroups into disk sets: a, b, c, d, etc. only makes it much more likely that one set will bottleneck while others are under-utilized.
2) Separate all the I/O components I mentioned above. Get to know your workload. Put the time into getting everything right.
3) Separate DB's from transaction logs due to the random vs sequential issue.
Well, I typically like # 3 (although I might like to separate tempdb too). However, I've seen VERY, VERY few application implementations that utilized one server and one DB. It's usually lots of DB's on each server. So, I'm curious how others choose to do this.
1) Separate all transaction logs onto their own RAID
OR
2) Separate out only the busiest transaction log
OR
3) Some hybrid of the two.
-
My main server has 2 heavily utilised large databases and a few smaller, less used ones.
We split the biggest database into 3 data files and put each one on a seperate Raid 5 drive. The log is on another raid 5 drive. The second database has its data and log files on seperate drives. All the other databases have their data files on one drive with their logs on another.
TempDB is on yet another drive.
We are planning to change some of those drives to Raid 10 in the next financial year, and split the biggest database up further.
You gain very little by moving all the tran logs to one drive. The main advantage to havng a tran log on a seperate drive is that the drive head is always in place to write, since writes are sequential.
It's probably a good idea to seperate out and isolate the busiest tran logs from the others.
I'd say, from your numbering above, that I wuold perfer 2 and 2.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
If you're using local disks, then I agree that object placement can offer real benefits.
I start to doubt the effectiveness of file placement once you start working with striped disks (such as RAID5 / 10). Unless you're splitting data and log between separate RAID arrays, putting them on separate disks within the SAME array won't necessarily give you much of an advantage. Because the LOGICAL data file and logfile devices are striped evenly over the same physical disks, they also share the same physical disk heads too. Admittedly, there are more of them, so throughput will be improved that way, but there is no guarantee that 2 sequential data and log reads/writes won't have to come from the same physical disk, and so disk wait time will still be a factor.
Our biggest server has some 25 databases, 3 of which are >100Gb, and two of those are >1Tb. The server sits on a single logical volume of 3 SAN disk shelves, totalling 51 physical disks, 6 parity disks for RAID4 (dual parity), and an advanced "write-anywhere" (WAFFLE) filesystem. Try controlled object placement on that setup!
All my data and log files sit on the same logical device, since this bears absolutely no relation to how the physical disks might be arranged in reality. I really would be wasting my time trying to administer any other setup.
To go by your original list, I'd probably try for something between option 1 and option 2. Certainly place binaries on a local drive (C: drive should be fine for SQLServer - it shouldn't suffer from paging). If you have a second local drive, place system DBs and tempdb on this. For everything else regarding the databases, by all means test and monitor, but I doubt you'll see much benefit by using deliberate placement if you're not using local disks.
-
Unless I'm forced otherwise I've always worked with the o/s and binaries seperated, although they could be on the same physical mirror just divided.
I always split tran logs, data and backups to seperate arrays, so for any typical production server I've worked on over the years ( which is a fairly strong oltp system ) I've used at least 4 physical arrays. Don't be tempted to split a physical array into virtual drives to get this seperation - will always prove counter productive.
If you split tempdb off make sure it has the same number of physical spindles available as it did on the data array otherwise you introduce a bottle neck.
Adding server memory ( and I mean significant memory ) will help offset poor disk subsystem performance. Avoiding raid 5 is always good < grin > More spindles = greater performance. Generally ( I've found ) read cache on controllers degrades performance, the log drive should only use write cache. Most controllers and especially SAN's don't give you choices these days which is why SAN's often perform so badly for oltp systems.
End of day you make your choices, monitor and trend, make changes , monitor ... etc. etc.
ps. all depends how much cash you have to play with.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Colin,
You stated that :
"If you split tempdb off make sure it has the same number of physical spindles available as it did on the data array otherwise you introduce a bottle neck."
Are you saying that if the Data files reside on a RAID 5 array with 5 physical disks and you move the TempDB to it's own RAID 1 or 1+0 or 0+1 array, performance would get worse? Let's say the RAID 1 array would be a straight mirrored pair of 2 disks and the 1+0 and 0+1 consist of 4 disks. These RAID sets would indeed have less spindles than the example RAID 5 set. Can you explain your statement in more depth for me?
-
Hi John
Normally you'd set tempdb on its own array because it has a lot of heavy useage, I monitor the throughput on all my databases so if tempdb is doing more than all the other put together then it might be an idea to split it out. So assume it currently resides on a 6 spindle raid 5 - that's good for reads but rubbish for writes, you'll get about 800 random i/o reads/sec and 200 writes ( theoretically and I've not done the exact math ) Now put tempdb on a raid 1 pair so there is one spindle available, now you can only get 125 i/o sec ( random ) read and write, so although the write performance has not degraded too much the read performance has been seriously bottlenecked - hence my comment about being careful.
Each disk can support a fixed number of reads and writes, random and sequential. Raid 5 has a heavy write overhead so technically you only get 25% of the theoretical throughput - this is covered in the inside sql server books, the admin guide ( 2000 ) and the performance tuning guide.
so amount of reads and writes is determined by number of availble spindles , this is usually total spindles less one for raid 5, less two for raid 6 and halved for raid 1 / 10
This is also one of the reasons a SAN may not perform well as no matter how you carve the disks ( or virtualise them to keep up with terminology !! ) the base i/o for the spindles cannot be changed - and the head can only be one place on the disk doing either a read or a write.
Hope that makes sense?
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Thanks for the detail Colin. Yes, it makes loads of sense. I am aware of the differences in read/write performance between RAID levels, but I guess that part that got me was introducing the bottleneck. Now I see where you are comming from. If the application was used to getting a certain level of read performance from the existing disk set, changing the number of spindles and possibly changing the performance of reads could prove noticiable from the application side. Thanks agian.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply