tempdb or transaction log on RAID 0+1
-
We are going to be moving some SQL Server files around to improve performance as we are heavily disk I/O bound. We are running SQL 2000 Standard and will be attaching a disk storage array with two separate channels. Each channel has 4 - 72GB SCSI 10000 RPM drives.
Was thinking of creating a RAID 1 with with three drives on one channel (72GB total available) and a hot spare and a RAID 0+1 on the other channel using all four drives for 140 GB total.
Our application is a ASP model with around 10 DB's varying in size between 30 MB - 5 GB in size. Around 200 + concurrent users are on at any one time. Space is not an issue. Our database has much more read activity than writes.
I will consider moving heavily used tables into a separate filegroup, etc, but our maintenance window is pretty tight so for now will look to move TEMPDB and log files.
Considering just performance, what is better to place on the RAID 0+1, the log files or TEMPDB? We hardly ever restart our SQL Server and our tempdb is up to 6 GB in size. There are other factors that may come into play regarding redundancy, etc, but looking for some feedback just considering performance. Thanks.
-
Hi,
Considering just performance, put your logs on the fastest disks you can.
The performance of the transaction log is probably the single biggest contributor to overall performance so ideally you want your transaction logs to perform as best as you can get them to.
Almost all I/O operations on a log file are sequential writes and you almost never read from logs. So this makes raid 1 (or 0+1) ideal for log files.
And make sure you don't put tempdb on the same drive as the logs. You want the log drive to be dedicated (in an ideal scenario one could even argue that you have one drive for each database's log). Putting the tempdb on the same drive as the log means that the disk heads will be moving around alot to satisfy the reads and writes inccurred by the tempdb. All of which is taking away from the ideal scenario of having the disk heads move in a sequential manner.
Hope that helps,
-
Thank you for your response. I will look to put tempdb on the raid 1 channel and the log files on the raid 0+1 channel.
One followup question - if I have a number of DB log files on the same drive, aren't the disk heads moving a round a lot as it shifts from one DB log file to the other? Or does it write sequentially in one log file and then move to the next log file and write sequentially? My guess is that although the disk heads are moving, it is nowhere near as much as mixing ldf and mdf files or ldf and tempdb on the same drive.
I would love to put each DB's log files on their own drive, but don't have the resources to do that.
Appreciate your assistance.
-
You are right in that having mulitple log files on the one disk can result in the heads moving around a lot. And one could argue for putting log files for each database on a seperate disk.
However, this isn't always practical or affordable. And as you say, the impact isn't as bad as having ldf and mdf files on the same drive. But certainly, if you had two large databases that processed lots of transactions I would consider seperating their log files or even put each large database on a dedicated server.
One option is to put your most important database mdf and ldf files on seperate disks and then put the system databases and your small (non-important) databases on the same disk (including the ldf files). With the system dbs and small user dbs you can get away with putting mdf and ldf files on the same disk. Activity on system dbs is minimal so performance shouldn't be affected and you don't even need to pay that much attention to the speed and size of the disks. You do lose some redundancy by not splitting the log files out but this isn't an issue for system dbs because they normally run in simple recovery mode anyway.
I guess all of this depends on how many databases you have on this server and whether it's as simple as identifying the most important one. As far as the business is concerned they are probably all important. And your job is to make sure they all run as fast as you can get them to run - typically on a shoestring budget
.Hope that helps,
-
Thank Karl for your response. All valuable things to consider. I agree some of the small DB's we could put both mdf and ldf on the same drive and not suffer too much.
Thanks again.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply