July 9, 2011 at 7:55 pm
Hello,
I have a Dell Server that has 6x 1TB HDD's. I am debating between 2 setups that I hope to maximize SQL OLTP performance, RAID10 vs 3x RAID1's.
I have read this benchmark by Kendal Van Dyke: http://www.kendalvandyke.com/2009/02/disk-performance-hands-on-part-6-raid.html. It seems RAID10 is the best and most prevalent, but would anyone support RAID1?
I read that it is recommended to separate the data, log, and tempdb onto separate spindles, to reduce disk contention, a 3x RAID1 setup for each category in my case. Is 6 HDD's a little to less for this setup? If so, how many HDD's would I need to begin looking into this setup?
Thanks
July 9, 2011 at 11:45 pm
You're a bit limited with 6 large disks: the RAID 1 configuration would result in 1TB tempdb, 1TB log, and 1TB data (and presumably, OS files). The RAID 10 configuration would result in 3TB for everything. What are your priorities here? It's almost heresy these days, but you could consider RAID 5 or 6.
edit: corrected 2TB -> 1TB
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 10, 2011 at 3:40 am
SQLkiwi (7/9/2011)
RAID 1 configuration would result in 2TB tempdb, 2TB log, and 2TB data (and presumably, OS files).
surely you mean 1TB tempdb, 1TB log and 1TB data.
RAID 0 (Striping) would provide the values above but with no redundancy
invulnarable27 how many drive bays does the server have total, i'm guessing it has more than six?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 10, 2011 at 6:32 am
Perry Whittle (7/10/2011)
surely you mean 1TB tempdb, 1TB log and 1TB data.
Yes I meant 1TB, and don't call me Shirley 🙂
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 10, 2011 at 6:48 am
SQLkiwi (7/10/2011)
Yes I meant 1TB, and don't call me Shirley 🙂
HA! Funny.
Also - Original Poster: drives which are that big sound like they are SATA drives. If you want to increase performance, SAS drives are quite a bit faster. Not only for controller throughput, but the spindle speed can be up to 15,000 RPMs. SSD's are faster still.
The catch is that the capacities are lower, so you may need more disks to make up the size of logical drives that you need, depending on the sizes of your database.
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
July 10, 2011 at 8:29 am
SQLkiwi (7/10/2011)
Perry Whittle (7/10/2011)
surely you mean 1TB tempdb, 1TB log and 1TB data.Yes I meant 1TB, and don't call me Shirley 🙂
😀 😉
Good point Jim on the disk sizes.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 10, 2011 at 3:58 pm
Hi Everyone,
Thank you for your responses. Sadly the server only has 6 drive bays maximum, and we are on a budget, so buying SAS or anything above that is kind of a no go. Speaking of SSD, I came across something called MaxIQ, very intriguing idea I want to implement in the future.
RAID1 would kind of be a waste huh? Log files and tempdb are a fraction of the data file. Somewhere around 10-20% from what I've read. Think I will stick to RAID10 for now.
Thank ppls!
July 11, 2011 at 8:30 am
You are screwed, no matter what you do, from an IO throughput perspective. Best is to max out memory and hope your database fits in RAM. 🙂
I would go with RAID1 for OS, binaries and all tlogs. Then RAID10 (or raid 5) for other 4 disks for all data files.
Most important (other than RAM max) is TUNE THE HELL OUT OF YOUR SYSTEM. Get a professional to help out with that.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 11, 2011 at 1:48 pm
I agree 100% with Kevin - the bottleneck will be with the drive speed. What I wouldn't give for an SSD.
Chris Powell
George: You're kidding.
Elroy: Nope.
George: Then lie to me and say you're kidding.
July 17, 2011 at 12:16 am
TheSQLGuru (7/11/2011)
I would go with RAID1 for OS, binaries and all tlogs. Then RAID10 (or raid 5) for other 4 disks for all data files.
Thanks for the suggestion. One question I had though is would you put the tempdb on the RAID 1 array w/ the OS, tlogs?
Or would you put the tempdb on the RAID10 array along w/ the data file?
I want to maximize performance and reduce as much disk contention as possible.
July 17, 2011 at 1:21 am
invulnarable27 (7/17/2011)
Or would you put the tempdb on the RAID10 array along w/ the data file?
This one.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 18, 2011 at 6:27 am
I think that in the first you should answer the following questions:
1. does your software actively use Tempdb ?
2. which DB Recovery mode you are going to use?
If you can't answer that question, so i can recommed you the following strucure:
1. Raid-1 for OS and Transaction files of all databases
2. RAID-10 (4 disks) for data file of all databases.
But my recommendation to make good baseline and create good monitor system to control loading on disks.
July 18, 2011 at 12:13 pm
Yes we actively use tempdb's. As for recovery model, we will be using Simple.
I would like a further explanation of why I should keep the tempdb on the RAID10 array along with the data file.
Why not the RAID1 array with the OS/tlogs?
July 18, 2011 at 2:50 pm
invulnarable27 (7/18/2011)
Yes we actively use tempdb's. As for recovery model, we will be using Simple.I would like a further explanation of why I should keep the tempdb on the RAID10 array along with the data file.
Why not the RAID1 array with the OS/tlogs?
The location of TempDB 'depends' on many factors. Among them are how tempdb dependent your queries are (Sort operations, temp table usage, normal in-memory operation constrained by memory or schema changes causing an overflow to tempdb and on and on). Some systems may not be as dependent on TempDB as others. The more dependent your queries or system are on TempDB, the faster the TempDB database needs to be. This is one piece of knowledge.
The other piece of knowledge is that RAID10 is a lot faster than RAID1 - because of the nature of striping. If 1/2 of the data is written to a disk and the other 1/2 of the data is written to another disk concurrently, then in theory, this operation may happen about twice as fast (again, depending on many things). In theory, it is twice as fast, in reality it may not be quite that fast, but is still faster than a not striping (RAID1).
So putting these two pieces of knowledge together: IF your queries are heavily TempDB dependent, then put them on faster disks. IF we don't know how your data is accessed (and we don't), then there are pretty good odds that the TempDB dependence has not been minimized by tuning, therefore put TempDB on faster disks as a default until you can test to see for yourself it that is really needed.
So although you COULD put TempDB in other locations, like the RAID1 disk set and you MAY also see better performance DEPENDING on many, many things, you are likely safer starting with TempDB on the R10 volume, along with the data. Odds are, you'll get better performance overall. And you will at least get good performance, so it is a safe and good starting place.
These are the reasons that I would recommend TempDB to start off living on the RAID10 set along with your data. Paul made the original recommendation and therefore may have different reasons.
Also, as a footnote, It is not recommended to have a production database in the Simple Recovery Model, unless you know the risks associated with it. It is best to have them in FULL and perform tlog backups regularly throughout the day.
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
July 18, 2011 at 3:35 pm
Jeez, we're not still flogging this dead horse are we 😀
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply