RAID configuration
-
Hello,
We are considering the following RAID configuration for our server.
We are after performance and redundacy. (Read/Write 60:40 ratio)
I would apprieciate any advise.
Many Thanks
RAID 1 - O/S, Page File, SQL SERVER application (2 X 36.4GB)
RAID 1 – Transaction log ( 2 X 36.4GB)
RAID 5 – SQL User Tables, system tables, TEMPDB (4 X 72.8GB)
Or
RAID 1 - O/S, Page File, SQL SERVER application (2 X 36.4GB) (Partitioned into 3 logical drives)
RAID 1 - Transaction log (2 X 72.8GB)
RAID 1 - SQL User Tables, system tables (2 X 72.8GB)
RAID 1 - TEMPDB (2 X 36.4GB)
-
I would use the second configuration, but change it to RAID 5, or even better RAID 10 for data files. In any case separate tempDB on its own array.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Thanks,
The server is restricted to 8 drives so the configurations I have suggested is all we can have.
Which ones out of the 2 would be best suited?
many thanks
-
Do you know anything else about the database structure and activity?
Is Tempdb really used much? Is there a lot of memory on the server? Are there big tables? Are there a lot of joins that application is using?
One of the recommendations is to put big tables and their non-clustered indexes on the separate filegroups on separate drives. Another one is to put frequently joined tables on separate drives. So instead of putting temdb on the dedicated array you may split the database between 2 drives. In any case restrict growth of Temdb to what you want it to be. I had a case when tempdb jumped from 10 MB to 9 GB when a report developer with read-only reporting login was trying something new (would not tell me what)
Yelena
Regards,Yelena Varsha
-
When we had RAID 5 on the drive with production DB data file, it caused serious performance problems - sometimes the application using the database ran so slow that it was impossible to work. We moved to RAID 10 and the problem was solved. Regarding TEMPDB, placing that on a separate drive really makes difference; what more, in your place I would consider a drive without RAID for the TEMPDB, since speed is of greatest importance there and you don't have to be afraid that you'd lose any data in case of failure. Otherwise, the option 2 seems to be fine.
HTH, Vladan
-
Still I would go for the second option. And make sure you have a good backup and recovery concept.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
I'm more with Frank on this.
I benchmarked different disk configs and found multi spindle RAID 10 the best (as in 6 or 8 disks upwards).
I'd go this way:
RAID 1 - O/S, Page File, SQL SERVER application (2 X 36.4GB) (Partitioned into 3 logical drives)
RAID 1+0 - Everything else (6 X 72) (or 6x36)
Make sure they're 15000 rpm disks
Unless you're expecting the server to get a massive load or your app is demanding or poorly written, the multiple spindle approach will be more flexible.
Also, where are your backups going?
-
Summary:
Go with your second option. My explanation is below, if you are interested.
Wow. Where to Start?
First of all, I partially agree with some of those who have already posted that you should avoid RAID 5. The issue with RAID 5 is its Write performance, which is very poor and can't be corrected by adding more spindles. In fact the best write performance that I have ever gotten out of RAID 5 was with 3 spindles and the performance went down with the 4th disk and stayed at that level until I hit the 14th disk in the array (the write performance at a Que depth of 1 was about 8 MB/s, with 10k SCSI3 drives). I used to play with tons of configurations at the Compaq Benchmarking lab and the benchmarking lab at my office, so I do know a good bit about disk array performance. Anyway, unless you know that your writes will never need more than 6 MB/s (a little breathing room) than RAID 5 is out.
Second, it is DBA 101 where you learn to always keep your Logs on Seperate arrays as your data for various reasons, including recoverability and performance. The recoverability part is self evident, as long as you are running in full recovery mode with the given DB, however the performance side can be a little bit harder to see. It is easy to see why you might go from a one disk config that has both data and log to a 2 disk config that has data on disk 1 and log on disk 2, but what about having both data and logs on both disks? Is that a good idea? The answer comes down to you knowing your database and server. The best outcome of this type of configuration is a stand alone SQL Server with 4 GB of ram (and the /3GB swtich on) that has a smaller well architected DB (less than 50 GB) that has almost all reads and a very few writes (including writes to temporary tables for hashing or sorting). Given this data, you would definitely get the best performance by having the drives in a single array and having your data and logs together (4 drives in a RAID 10 config or 3 in RAID 5). However your data suggests that you have a "(Read/Write 60:40 ratio)". I have an honest doubt that it will be that high of writes, but I will use the ratio for now. With a configuration of having all data, logs and tempdb on a single array, there is a high probability that you will incur disk head flutter, since your que could get pretty deep. I have seen this type of scenario reduce very powerful machines to a crawl. Therefore, I would have to disagree with the approach of putting all SQL info on a 6 disk RAID 10 array. I would much rather see you go with your second option, which will minimize any possibility of I/O contention.
Having said all of that, the biggest impact on performance will be proper Database design, maintenance, and well written SQL. I can take a porrly designed database that runs on $3,000,000 worth of equipment and outperform it with a desktop PC and a properly designed and accessed database. In fact, I have done that to prove a point at a customer's site about 5 years ago.
Again, without knowing a whole lot more details this is the best advice I can give. If you really are a newbie and you are concerned with performance then I would ask a seasoned DBA (Not an App. Developer) to review your DB design, etc. Doing it earlier rather than later could save you a whole lot of time and frustration, not to mention customers.
I hope this helps.
-
Just to be complete, RAID 5 is totally different for an OLAP environment, since I suspect it there giving better performance than any other RAID level.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
I agree that ordinarily multiple arrays will be better than single array, but surely it depends on database size, design etc. Server limitations also play a part.
Given an 8 disk capacity server, it'll probly only have one RAID card in it, and maybe onbaord at that (See Compaq Smart Array 5i vs a SA6400 series in bigger server). Multiple arrays should tax the RAID card more than one big one especially with caching on teh card and on th disks. I'd split my arrays further if I had bigger servers and more cards to play with.
Is the server to have one big db or several small ones?
Where are backups going?
In our (not generally...) case a 4 disk RAID 1+0 (36GBs) + mirrored log array (72GBs) out was slower than single 6 disk RAID 1+0 (36GBs). In the case above with a max of 8 disks, I stand by a 6 disk RAID 1+0.
-
Firstly thanks to all for your suggestions / comments.
The server will hold 1 database which is currently approx 4GB in size and dont envisage it growing to more that 40GB in the next 2/3 years. (According to previous analysis)
The server has 2 X Exeon 3.2MHz processors.
3GB Memory - 1GB O/S 2GB SQL Server
SQL Standard Edition
Windows 2000 or 2003 (nothing has been installed as yet)
Smart Array 64mb on board and 128mb extra add on.
The hardware including the HDD have been purchased so I am stuck with the the following:
4 X 36.4GB
4 X 72.8GB
Backups are to go onto partioned volume which will hold o/s etc. and then backup software will back up to tapes. The local back-ups will be removed.
The database is a CRM system bought in and developed by an external company.
Approx 130 users will be accessing the database, but not necessarly to update / insert data at the same time. Report will be run by a few users. I actually do agree with Daren, writes are not going to be any near 40%.
I think option 2 is what I would like to go for, as this will give me greater performance with some redundancy.
However the RAID 5 option gives me more space but I apprieciate I will lose performance.
So, considering the above, is option 2 the best?
Thanks Again.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply