May 7, 2007 at 4:50 pm
The only good thing that can be said of RAID 5 or 6 is that it's cheap it all falls down after that. RAID 5 or 6 both perform very poorly at sequential read and write performance as well as Random Read and Write performance. RAID 1+0 or 0+1 has excellent capability in both Sequential and Random read and write performance. RAID 5 or 6 again come up short in terms of availability especially as you scale out your arrays with either a DAS or SAN based system. RAID 5 can only lose 1 drive before your data is unprotected, RAID 6 can only lose 2. RAID 0+1, 1+0 can lose up to half the drives in an array without incurring a failure. As an example if you have two shelfs with 12 drives in each shelf in a RAID 0+1, 1+0 Array with the Mirror sets being across the shelfs and the stripe sets contained within the shelfs you can lose an entire shelf without affecting the operations of the server, RAID 5 or 6 simply cannot survive in this scenario. How likely is this to happen? I've seen it happen on one occasion with a SAN system that contained over 160 disks at the time. Every single RAID 1+0, 0+1 array came back on line when the system was restared after a combination power outage and generator failure, none of the RAID 5/6 arrays came back on line from this. We eventually recovered the RAID 5/6 volumes without having to go to backup but the pucker factor was definately there.
Regarding seperate physical arrays for either the logfiles or tempdb we'll assume that you are going to use RAID 1+0, 0+1 for all arrays except OS and Application binaries. I'll tackle the logfiles first and we'll save the tempdb for later. Most RAID controllers when configured for RAID 1+0, 0+1 will scale up performance wise in a mostly linear fashion as you add drives to the array, there are some exceptions to this but in almost every case the manufacturer will have this documented and provide guidance on how to configure the logfile array. In no instances should the recomendation for the minimum number of drives in the logfile array be less than what the manufacturer recomends, if you cannot meet the minimum requirements for the logfile array then it is pointless to create it and the logfiles should be created in the same array as the datafiles if needed you can add additional drives to the array to increase capacity/performance. If you do choose to go down the path of a seperate array of physical disks for the logfiles you should have a good understanding of the IO requirements of the expected task for you server and the logfiles themselves. Will there be a lot of transactional writes, and or updates to the databases? If so the log volume will need to be higher performance than for a database application that mainly reads from the database. As a general rule for array sizing in a RAID 1+0, 0+1 logfile array there should be at least 4 disks/spindles for every server using the array with 8 being optimal. Sound like a lot? It's not a 4 spindle RAID 1+0, 0+1 Array is the functional performance equivalant of a 2 spindle RAID 0 Array. Generally the logfile array should have better performance than the data array so you should always choose the number of spindles in this array based on the performance requirements and not for capacity. In no instance do you want your logfiles starving on a 2 spindle RAID 1 Array while your datafiles are underutilizing a 10 spindle RAID 10 Array. Your probably thinking "gee this could get expensiver really fast" and you'd be correct, you may be wondering what would happen if you took the extra spindles and simply added them to the data Array and put the logfiles there. Would you even be able to notice the difference on a benchmark? What kind of efficiencies are gained by having a larger pool of storage to allocate between the Data and Log volumes? In most cases the performance difference is negligable as long as the Array has enough spindles to support the IO requirements of the database application, as a rule you would not want to use less than 6 spindles per server in this type of configuration with 10 being optimal.
Should the tempdb be placed on a seperated physical array? The real answer to this is......it depends. Microsoft although stating that you can move the tempdb to a seperate physical array doesn't actually recomened it, and recomends serious analysis and performance testing should you choose to go down this path. In any case the array for the tempdb would be subject to the same general performance rules as the logfile array, it would also need to be large enouge to contain the tempdb files at the largest size you can expect to see them. I've seen 50+GB tempdbs during database alters and restores. If you already have sufficiently beefy log and data arrays then it is pointless to stick the tempdb on a 2 spindle RAID 1 array if your goal is to increase the performance of the tempdb. I've seen plenty of people do this and when asked for the reasoning behind it to a man they all fell on the "Because it's a best practice!" excuse.
Conclusion:
RAID 0: Lack of fault tolerance makes it unsuitable for enterprise applications
RAID 1: OK for OS and Application binaries, if you are considering this for a database due to limitations in server capacity you should consider another server.
RAID 5: Poor performance and Fault tolerance make it unsuitable for enterprise applications
RAID 6: See RAID 5
RAID 10/01 (0+1,1+0): Excellent Performance/Availability make this RAID Level Ideal for enterprise applications
RAID 15/51: Poor Performance, Excellent Availability, performance makes this unsuitable for database applications. Not widely available.
Reference:
MSSQL I/O subsystem requirements
RAID Level Performance/Availability Comparison Chart
Example Mfg Log Config Document
Joseph Martin
EDS
Systems Administrator Sr.
May 7, 2007 at 6:45 pm
Joseph,
Good points, but the reality with RAID 1+0 isn't that you can lose half the drives. You may be able to, but not necessarily.
If you have drives 1, 2, 3 mirrored to drives 4, 5, 6, then you cannot lose 1 and 4 if they are mirrored to each other. You could lose 1, 2, and 3, but if you lost two sides of a mirror, you're down.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
May 7, 2007 at 7:05 pm
Absolutely correct, however the likelyhood that you would lose both disks in the same mirrorset at the same time is extremely unlikely, especially as the array grows in spindles. The more spindles in the array with RAID 1+0 the more distributed the failures are likely to be so the larger a RAID 10 array becomes the fault tolerance also scales up although not in a 1 to 1 relationship. For example in a RAID 10 array with 100+ disks you could have an event where several disks fail over the weekend and the array is unaffected. With RAID 5 or RAID 6 you would be out of business. Most modern controllers do by default split the mirror across shelves, in that case you can, and do lose half your disks if you only have two shelves, granted the system this happened on had six shelves at the time of the failure, which means we only lost 1/6 of the disks in the RAID 10 arrays but the RAID 10 arrays were able to survive this and came up, a RAID 5 or RAID 6 array would never have the ability to withstand this type of failure.
May 7, 2007 at 7:06 pm
just, wow.
FTW!
May 8, 2007 at 7:47 am
Well. Unless were talking SAN I still don't see RAID 10 being the best choice of spending your $$$.
There's so much more then disks that can go wrong. USING RAID 10 and telling me it's for DR would not get me to sign off on the invoice. Telling me it's for performance and that you have proof we need this performance and can ulilize it will. RAID 10 for DR is like how they build the Titanic. They build those big bulkheads that went from the bottom to about the middle of the boat but didn't seal them off. So as each chamber created by a bulkhead filled up with water it just spilled over the top to fill up the next bulkhead. Thus the boat sunk.
Having a great disk (-SAN) setup for your SQL server is nice but if anything else goes wrong with the system you still have a large pile of smoking crap. All the RAID 10 in the world isn't going to save you from a bad controller or a bad MB that causes bad data to be written to the array.
I will not argue the benifits of RAID 10 for performance. BUT for DR isn't going to fly. There's so many other ways to handle DR for SQL that throwing all your money at the disk is in my opinion just silly. Besides if your ready to spend on RAID 10 for one box.... just skip it and get a SAN and do it right. Then your on a better road to well'svile.
Sean~
May 8, 2007 at 8:24 am
Not to be contrary, but it seems like faulty logic, re the titanic and RAID 10.
For one, its a bit of an emotional response to the idea that RAID card's MOBO failures will destroy the data on the disk is the same thing as not enough lifeboats and lack of avoidance of icebergs. Actually as far as I can tell, most sea voyages are quite safe, safer than air travel, except for the sea creatures themselves.
The cost of a good RAID 10, a couple grand for its accepted life of the 2-3 years of 'live' use, over the 20-100k for a good SAN, before outlaying that kind of coin, it could be viewed as starting point, not neccessarily the final destination.
Where the SAN actually seems make more sense would be with those others... "O" rdms people... where datawarehousing is more intrinsicly deployable. But for front line DB's a nice RAID 10 and as I stated having two of them even with one just sitting idle, replicating the data seems like a slick solution.
That said, you want two pairs of mirrors for each box one for the OS and another for App space all connected to a SAN, I'll build it for you. If you ever test raid 5 or 6, its slow at least to me, let alone under heavy stress. Again it will work. And again if thats the budget then so be it.
My preference for the RAID 10 over the SAN is i guess the quality of the subcomponents. Not all drives are the same quality and without looking into their preferred working temp ranges and expected life, no matter which RAID or DR solution you choose if most or ALL the drives go, it doesn't really matter. Which is more of the concern, i think.
Hitachi not to slight them but had a huge number of drive failures, I think it was even litigated that their drives had huge hd controller problems, and settled in some states.
When you typically source a SAN, the stock drives provided seem to generally be ones that eithr run too hot for thier own good and their close proximity seems to suggest planning for better temperture control.
Call me fussy, but I tend to like to open the hood and check for excellent sub components making sure the SAN isn't full of soon to be CC's oem's.
May 8, 2007 at 8:45 am
I agree with hundreds of disks it's unlikely you'll lose both of the mirrored pair, but for smaller installations 4-10 disks, it is a possibility. No idea on probabilities, but you could lose 2 disks and be out of business, same as RAID 5.
Course you can have hot spares, in R5 or R10, and reduce your likelihood of losing data.
The thing about SANs is that all those disks increases the changes that on any day you'll have a failure. I've had a few SANs in companies and it seemed like there was a replacement every week of a drive if not more often. It's something you need to be sure that you plan for and have lots of spares around. Not necessarily a bad thing, but it's a budget item you have to plan for.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
May 8, 2007 at 9:12 am
....Around the time of the first ipod...
For those who like trivia, hitachi drives were intially an arm of IBM, but oddly IBM sold off that division.
IBM STOPPED making Hard drives.
From the website.....
"1956 - FIRST MAGNETIC HARD DISK. IBM introduces the world's first magnetic hard disk for data storage. RAMAC (or Random Access Method of Accounting and Control) offers unprecedented performance by permitting random access to any of the million characters distributed over both sides of 50 two-foot-diameter disks. Produced in San Jose, California, IBM's first hard disk stored about 2,000 bits of data per square inch and had a purchase price of about $10,000 per megabyte. By 1997, the cost of storing a megabyte had dropped to around ten cents."
They invented it. But stopped making them.
This was somewhere around the time of production issues.
The fact that the "small" drives made by hitatchi and IBM, ended up in Apple Ipods for the first generation, must smack of a bit of irony.
Interestingly enough Apple chose another vendor for its future generation Ipod's.
May 8, 2007 at 1:57 pm
From reading the posts here I think the real question might be...
How much money do you think you are really saving with RAID 5?
May 10, 2007 at 7:17 am
"No idea on probabilities..." Steve, the probability that the second disk failure will kill your n-disk RAID10 array is 1 in n-1. So (approximately) 33% if you have 4 disks, 20% if you have 6, 14% if you have 8, and 11% if you have 10. You're right - it certainly is (more than) a possibility!
John
May 10, 2007 at 7:23 am
Just a note on the probabilities of loosing both disks in a mirrored pair. We have experienced this twice in the last two weeks. Once each on two different servers. We have attributed it to equipment age. It was also surprising since amongst the System Engineers at my shop, 6 of us, we have combined experience of 100+ years. To date none of us has ever seen this before ! So it does happen. Also the oldest of the equipment was just about 3 years and a couple of months.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
May 11, 2007 at 11:47 am
Most typically, we've implemented RAID 1 for OS+logs and RAID 5 for data. Too many places don't have access to SAN due to cost, and SANs require their own set of expertise in administering them. RAID 5 has also gotten a bit of increased usage with my customers because of RoHS. RoHS compliant drives tend to have smaller capacities, so we need to use RAID 5 to meet disk storage requirements. All said, its not the best arrangement, but it works remarkably well at a cost that gets by the one holding the purse strings.
That said, here's some rules that I've used in the past:
Viewing 12 posts - 31 through 41 (of 41 total)
You must be logged in to reply to this topic. Login to reply