February 18, 2011 at 10:06 am
Tom.Thomson
dev-638705
Nice question, although RAID5 is a not really a good choice for databases ! (except limited cases with mostly read-only databases).
Better use RAID 01 (i.e. RAID0-aggregate of RAID1-mirrored drives), which is good for reading, and also good for writing your database stuff.
If your data is sufficiently important to need protection and you don't want access to be hopelessly slow during recovery if you ever lose a disc and have to replace it, don't use RAID 5 even for completely read only databases!
Can't agree here. This is like saying the only tool in your toolbox is a hammer. Very special jobs get a bigger hammer.
There is a purpose for each RAID level. See http://technet.microsoft.com/en-us/library/cc966534.aspx paying particular attention to #5 and #7. Anything that has a high volume of writes should be on RAID 1+0, especially in a high volume OLTP system.
You can also refer to http://searchsqlserver.techtarget.com/tip/Optimize-disk-configuration-in-SQL-Server which tells that the data files (.mdf and .ndf) shoould be on at least a RAID 5, in some cases RAID 50. I have not played with RAID 6 yet, but hope to soon.
It sounds like Tom is making his decesion based solely upon the restore time during startup. At least on my systems, startup happens occaisionally, while users wanting to generate reports or pull data for any variety of reasons want it as fast as possible. Thus the reason for multiple types of arrays.
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
February 18, 2011 at 10:22 am
Thanks for the question! I was surprised to see the number of incorrect answers, I thought this was pretty much common knowledge.
February 18, 2011 at 10:29 am
Tom.Thomson (2/18/2011)
dev-638705 (2/18/2011)
Nice question, although RAID5 is a not really a good choice for databases ! (except limited cases with mostly read-only databases).If your data is sufficiently important to need protection and you don't want access to be hopelessly slow during recovery if you ever lose a disc and have to replace it, don't use RAID 5 even for completely read only databases!
I haven't found access to the database while a RAID5 array was recovering from a failed drive to be hopelessly slow, but I am sure it depends a lot on your hardware. (And in most cases the recovery time is less than an hour.)
In fact I get better performance from RAID5 than RAID10 for everything but random writes. (Our databases have very few random writes, so that isn't an issue.) RAID6 even competes fairly well with RAID10, again excluding random writes, at least with current HP DAS controllers. (SmartArray P812)
February 18, 2011 at 11:40 am
sjimmo (2/18/2011)
Tom.Thomson
dev-638705
Nice question, although RAID5 is a not really a good choice for databases ! (except limited cases with mostly read-only databases).
Better use RAID 01 (i.e. RAID0-aggregate of RAID1-mirrored drives), which is good for reading, and also good for writing your database stuff.
If your data is sufficiently important to need protection and you don't want access to be hopelessly slow during recovery if you ever lose a disc and have to replace it, don't use RAID 5 even for completely read only databases!
Can't agree here. This is like saying the only tool in your toolbox is a hammer. Very special jobs get a bigger hammer.
There is a purpose for each RAID level. See http://technet.microsoft.com/en-us/library/cc966534.aspx paying particular attention to #5 and #7. Anything that has a high volume of writes should be on RAID 1+0, especially in a high volume OLTP system.
Given that you appear to believe that I disagree with somethingthing in #5 or #7 there I think you have badly misunderstood what I wrote. I was objecting to someone (a) advocating the general use of RAID 6 and (b) describing RAID01 (RAID0+1) as RAID10 (RAID1+0); and also advocating the use of RAID1+0 for everything. #5 and #7 on the page you reference advocate the use of RAID 1+0.
You can also refer to http://searchsqlserver.techtarget.com/tip/Optimize-disk-configuration-in-SQL-Server which tells that the data files (.mdf and .ndf) shoould be on at least a RAID 5, in some cases RAID 50. I have not played with RAID 6 yet, but hope to soon.
It sounds like Tom is making his decesion based solely upon the restore time during startup. At least on my systems, startup happens occaisionally, while users wanting to generate reports or pull data for any variety of reasons want it as fast as possible. Thus the reason for multiple types of arrays.
No, I base my decisions on the two critical issues: reliability of recoverability and consistent good performance (including during recovery from single disc failures); anyone who chooses RAID 5 over RAID 10 to save a trivial amount of upfront cost at the risk of compromising those key attributes has presumably not done the mathematics (probably neither on the costs nor on the reliability). I strongly recomment reading BAARF Manifesto and the various things linked from there for anyone who believes that RAID 5 or RAID 4 or RAID 3 is something sensible to use for database work (the cost of discs and controllers in the early 90s made it an attractive option, price-wise, perhaps enough so to justify sacrificing some reliablity/recoverability/performance; that ceased to be true quite a long time ago).
Tom
February 18, 2011 at 12:40 pm
Tom
I strongly recomment reading BAARF Manifesto and the various things linked from there
Sorry, after reading a majority of things on BAARF, we will have to agree to disagree. At a basic level of 3 drives, yes - RAID 5 loses 1/3 of each drive to parity as well as information required to rebuild a failed drive. But, a was stated on BAARF you can adjust the restore in order to obtain a balance and impact production at an acceptable level or you have the choice of having production off for the amount of time it takes to rebuild. All of this is a business decision. Overall performance of a database, as also stated in BARF from an Oracle employee (which appears to be a majority of BAARF (ORACLE)) you can spend the time to design your storage system in a way to optimize your system. Essentially, IMHO, simply stating that you want to use one RAID array is the lazy way out. I will admit that I am pretty lazy, which is why I put the work in up front to do all of the engineering and design work so that when things are done the first time I don't have to go back. Able to drink more coffee that way.
Who knows, maybe we will see a class on this at a SQL Saturday and I will revise my thinking. But believe me, my decisions are not driven by cost. Simply performance.
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
February 18, 2011 at 5:39 pm
sjimmo (2/18/2011)
I strongly recomment reading BAARF Manifesto and the various things linked from there
Sorry, after reading a majority of things on BAARF, we will have to agree to disagree.
OK, I don't have a problem with that.
Essentially, IMHO, simply stating that you want to use one RAID array is the lazy way out. I will admit that I am pretty lazy, which is why I put the work in up front to do all of the engineering and design work so that when things are done the first time I don't have to go back. Able to drink more coffee that way.
Putting everything on one array will almost always be completely wrong. Tempdb should probably have an array to itself, and the transaction log certainly should. How many other arrays are used will depend on how the access patterns to the data are, and make take some hard work to work out. There's no way I would suggest using a single array for everything; I would suggest using RAID 10 for each array, because the advantages both in performance terms and in reliablity terms are easy to calculate. Of course there will be cases where that isn't required, because the performance required is way below what that will deliver and frequent backups with full recovery model will be good enough for the required availability and recoverability - but in those cases what is the point of RAID 5 isn't needed either.
Who knows, maybe we will see a class on this at a SQL Saturday and I will revise my thinking. But believe me, my decisions are not driven by cost. Simply performance.
That somewhat throws me. How do you get better performance out of RAID 5 than out of RAID 10 for the same data size? An array of 2N discs as RAID 10 delivers the same available data volume as an arrary or N+1 discs as RAID 5 and delivers both much better READ performance and much better WRITE performance as well as greater reliabilty and less performance degradation during recovery.
Tom
February 18, 2011 at 5:44 pm
Tom.Thomson (2/18/2011)
That somewhat throws me. How do you get better performance out of RAID 5 than out of RAID 10 for the same data size? An array of 2N discs as RAID 10 delivers the same available data volume as an arrary or N+1 discs as RAID 5 and delivers both much better READ performance and much better WRITE performance as well as greater reliability and less performance degradation during recovery.
That isn't my experience, for reads I tend to get about the same performance from N+1 RAID5 and 2N RAID10. (Same with sequential writes.) Once you go to random writes RAID10 wins hands down, but costs twice as much. (And not just when you buy the equipment but everyday in additional electricity, cooling, rack space, etc. expenses.)
February 18, 2011 at 6:50 pm
Tom,
Putting everything on one array will almost always be completely wrong. Tempdb should probably have an array to itself, and the transaction log certainly should. How many other arrays are used will depend on how the access patterns to the data are, and make take some hard work to work out.
We do completely agree here:-D
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
February 19, 2011 at 6:25 pm
If you want to get back faster on your feet after a disk failure, I would think that RAID 10 is the better choice,
but you don't argue about RAID 50...?
I tought that RAID 50 was the best choice against RAID 0, 1, 5 and 10: Big storage capacity from RAID 5, possibility to get back fast after one dick failure from RAID 0 and always having a set "backup" ( from RAID 5) to make your DATA safe from loosing them
So why don't you argue about RAID 50? Too slow?
February 21, 2011 at 10:43 am
Depending on hardware and the application, RAID 5 can perform BETTER than RAID 1/0. EMC has done benchmarking on their Clariions (which many of you have) that shows that high bandwidth (large sequential I/O) operations might best be served by RAID 5.
http://www.stlssug.org/docs/Best_Practices_for_Performance.pdf
Obviously, this may represent an exception to normal database use, but it should remind us that best practices have caveats and each application should be carefully evaluated.
And 26% OF people missed this? 🙂 Surprising. I guess too many of us are used to trick questions...
Peter Trast
Microsoft Certified ...(insert many literal strings here)
Microsoft Design Architect with Alexander Open Systems
February 21, 2011 at 10:52 am
Haven't seen much written on RAId 50. I suspect it suffers the same write penalty that Raid 5 has.
Raid 5 worked well for me for years, and continues to do so for many people. It's not what I recommend, but when cost becomes a factor, then consider it. However make sure you have 1 or 2 spare drives, and I might make sure that I get drives from different batches or manufacturers to hedge against multiple failures.
February 21, 2011 at 11:46 am
Steve Jones - SSC Editor (2/21/2011)
Haven't seen much written on RAId 50. I suspect it suffers the same write penalty that Raid 5 has.
I'd agree and it does, but RAID 50 can be handy when you just literally don't have enough SAN slots for the amount of space you need for a 2N RAID 10. It helps protect you from multiple drive failures, though.
The place we implemented at was 7 drives/ RAID 5 which was striped to ... errr, I don't remember anymore. I want to say we had 8 RAID 5 sections? Something like that. It ran rather speedy.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 21, 2011 at 12:00 pm
Peter Trast (2/21/2011)
Depending on hardware and the application, RAID 5 can perform BETTER than RAID 1/0. EMC has done benchmarking on their Clariions (which many of you have) that shows that high bandwidth (large sequential I/O) operations might best be served by RAID 5.http://www.stlssug.org/docs/Best_Practices_for_Performance.pdf
Obviously, this may represent an exception to normal database use, but it should remind us that best practices have caveats and each application should be carefully evaluated.
In the document you reference, the diagram and text at the bottom of page 8 makes it clear that RAID 10 (at equal capacity) is best for serial READs (although it does cost more) and at the top of the same page it puts it quite bluntly: even on equal spindle count RAID 10 is slightly better than RAID 5 once there are 20% writes injected amongst serial reads, and at equal capacity RAID 10 offers much higher performace even for pure serial reads than RAID 5.
The one case where RAID 5 can outperform RAID 10 is on serial writes (this is confirmed by the document you reference) where RAID 10 has 2N discs to synchronise and RAID 5 has only N+1. So it may be advantageous to use RAID 5 for serial write dominated activity - but apart from the transaction log, where I would be more concerned about READ times and recoverability even though writing is (hopefully) more frequent than reading, I can't see there being any such activity in a database.
Thanks for pointing that document - it's another useful description of the issues, and I reckon we can't have too many of those.
And 26% OF people missed this? 🙂 Surprising. I guess too many of us are used to trick questions...
Sadly I suspect you may be right.;-)
Tom
February 22, 2011 at 8:15 am
tilew-948340 (2/19/2011)
If you want to get back faster on your feet after a disk failure, I would think that RAID 10 is the better choice,but you don't argue about RAID 50...?
I tought that RAID 50 was the best choice against RAID 0, 1, 5 and 10: Big storage capacity from RAID 5, possibility to get back fast after one dick failure from RAID 0 and always having a set "backup" ( from RAID 5) to make your DATA safe from loosing them
So why don't you argue about RAID 50? Too slow?
I don't understand what you mean by possible to get back fast from one disc failure from RAID 0: any single disc failure is fatal in RAID 0 (RAID 0 is about capacity and performance, with total disregard for reliability) that's why the "discs" used in the RAID 0 which makes up RAID 50 or RAID 10 are actually redundant arrays - if they weren't the RAID 0 would be much less reliable than a single disc.
RAID 50 has the same random write penalty as RAID 5, and like RAID 5 it is cheaper than, provides less sure recovery than, takes longer to recover from a single disc failure than, and will deliver much worse performance than a RAID 10 with the same capacity (except on a workload that is thoroughly dominated by serial writes). It is a bunch or RAID 5 arrays, so recovery from a single disc failure is exactly recovery from a single disc failure in a RAID 5 - its advantage doing the whole thing as a single RAID 5 is that recovery only involves the discs in the individual RAID 5 array which is not all the discs in the RAID 50, and its disadvantage is that it costs more.
Looking at a reasonable RAID 50 configuration one might see 4 RAID 5 arrays each containing 5 discs of which one is a hot spare, using 20 discs in all to provide a 12 disc capacity; the same capacity RAID 10 with a single hot spare uses 25 discs, so it's only 25% more expensive in discs. For that extra 25% you get better performance on almost any workload (the only exception is when more than 80% of the workload is serial write transfers) and very much better performance on most workloads. You also get faster recovery from single disc failures, and a much lower chance of multiple failures causing data loss.
There will be cases where the increased cost matters enough not to go for RAID 10, but they will be rare. There may be cases where a serial write dominated workload isn't reliability critical (as it is for the transaction log) in which case the extra cost might as well be saved, but I have never seen such a case where it would make sense to use a relational database to handle it.
Finally, there will be cases where the extra cost of RAID 10 is significantly different from 25%:
(a) when the individual RAID 5 clusters are much bigger (but then the reliability and recoverability of the RAID 50 are greatly reduced; it's still better than shoving the whole thing into 1 RAID 5 though)
(b) when it is required that the individual RAID 5 arrays are on separate controllers (so the RAID 0 is done in software on the server) to reduce the stress on the controllers, in which case the same sort of thing has to be done with RAID 10, probably splitting the RAID 10 into about two thirds of the number or arrays that form the RAID 50 as the RAID 10 is less stressed for a given workload than a RAID 5 of equal capacity, so that the example used above would need 3 hot spares in the RAID10+0 configration, as opposed to 4 in the RAID 5+0 configuration, so maybe the RAID10+0 would cost 35% more in discs; but in situations requiring that sort of reliability you probably have to use 2 hot spares in the RAID 5s, because their recovery time is so much higher than that of RAID 10, so maybe the RAID 10+0 costs only 12.5% more in discs than the RAID 5+0.
Tom
February 22, 2011 at 3:03 pm
I dug around a bit and came across a couple of interesting papers from about 4 years ago (I guess I missed them then because I was up to my neck in upheavals at Neos Interactive) Disk failures in the real world: What does an MTTF of 1,000,000 hours mean to you?
The results of quite an extensive survey suggest that real disc MTBFs for "enterprise" type discs are about 33% of what the suppliers claim, that burn in does more bad than good (failure rates grow steadily as discs get older), and that the liklihood of failure of a second disc in a RAID 5 configuration is much higher immediately after failure of one disc than at other times and gradually falls back to it's "other times" value over a period of time (but the vendors claim there is no auto-correlation, ie this effect does not exists. so that the risk of data loss can be calculated on the basis of exponentially distributed failures rather than allowing for the autocorrelation).
That has made me distrust RAID 5 even more than I already did.
And then there's Failure Trends in a Large Disk Drive Population from Google (survey involving vast numbers of discs) which suggests that cheap PATA and SATA discs are as reliable as enterprise discs. Maybe we could save money by using those instead of enterprise discs? Or maybe performance would be an issue (or maybe not, as the smaller capacities would mean more spindles to offset the lower rotation speed and higher seek times).
Tom
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply