July 19, 2006 at 9:28 am
We've been running a 30gb OLTP database on a RAID 5 with only 5 drives for the past 6 months, and I'm getting tired of hearing the complaints about performance. It looks like I'll finally have a (small) budget to do some upgrades. Unfortunately, I doubt it'll be enough for a new server, and the constraints on our current one are, well, limiting. The person who set this system up is gone, and I'm a bit abashed at saying (among such experienced pros) this is my first attempt at a RAID configuration. Well, here goes:
Current specs:
1 - SQL Server 2000 Std SP3 (gonna push Ent upgrade through soon, i hope)
1 - Windows Server 2003 Std SP1
2 - Xeon 3GHz (though for some reason windows sees it as 4 processors)
3 - 1gb @ 2100 (yeah, i know... )
1 - 1gb @ 3200 (and i know that, too...)
1 - 8 port SATA RAID controller
5 - 160GB SATA 7200rpm 8mb
And that's it! We have two logical drives, one for the OS (100gb), the other for SQL Server (500gb).
Assuming I'm confined to the single 8-port controller, how would I best go about upgrading my drives? What configuration would be best? I understand that it would be highly beneficial to put the transaction log on it's own raid, but is that even possible? We have a single table that gets a high percentage of the write traffic, making it a good candidate for its own raid, but is that possible? I've also toyed with the idea of putting the tranlog on an external Lacie, using USB 2 to keep the speed up. Bad idea?
July 20, 2006 at 9:29 am
You'll have hyperthreading which is why sql server sees 4 procs - this is normal and good ( don't let anyone tell you to turn off hyperthreading it works fine under w2003 - there were some problems with w2000 )
Be wary of pushing a sql ent upgrad e without doing the o/s, you might gain less than you expect!
OK I expect I'll get blasted about sata disks but .. I'd get rid of them the 7.2k spin speed is a really big limit on i/o. However, I don't expect you can so add more spindles and use raid 10. Yes trans logs on raid 1 dedicated would be very good - tran logs on raid 5 is the best way to cripple performance!!
Yup external usb for prod box is a very bad idea indeed.
A 5 disk raid 5 will give you not much more than equivilent a single disk for writes, adding 3 disks and moving to raid 10 will almost quadruple your write performance ( depending on the array controller ) but will not improve reads particularly, and I will not debate raid i/o performance - it's well documented almost everywhere and much depends upon the implementation of the array controller raid.
I'd say you were between a rock and a hard place - try to get another sata card and more spindles.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
July 20, 2006 at 10:51 am
Right now we have only 1 array controller with the 5 drives on a single RAID 5, 2 logical drives. What if we either added another controller or replaced the current one with a controller capable of multiple arrays, put the db on a 6-disk RAID 5 or 10, and the OS and tranlog on a 2-disk RAID 1 together?
July 21, 2006 at 2:40 am
as I say , more spindles and raid 10 will improve things. Changing to 15k scssi disks would work wonders mind, a 15k disk will support maybe twice the i/o per second than a 7.2k disk.
Splitting tran logs to a seperate raid, not a partition, will also help.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply