Hardware & RAID Configuration - What''s the best
-
Hi,
I'm about to setup a new production enviroment which relies heavily on SQL Server 2005 Enterprise to manage a processing queue.
There will be lots of BLOB data in a form of XML exchanges (a separated file was designed for those) and lots of transactional data.
We have 6 hard drives to create a RAID 5 for data, and I was suggesting one large drive for Log files. Our network adminstrator is suggesting to break them in 2 raid 5 (each with 3 hds) for better performance.
Thanks for your comments, they are HIGHLY apprecitated!
all the best,
dreeZ
-
If you on a budget, RAID 5 is the best option for SQL.
The issue with 5 is the overhead of the parity bit being written.
Ideally, you should have a RAID 1/0 set for data, one of logs and if need be, one for blobs. This is the expensive route though.
In your case, I would say split them as suggested. It allows more spindles to be used concurrently.
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
-
I suggest you buy more disks, raid 5 is absolutely no use except for a read only database, even for the budget user raid 5 is a waste of time.
Transaction logs must be on raid 1 or 10 only - never raid 5.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
I'm curious as to your reasoning for raid 5 being of absolutely no use?
-
-
The main drawback for RAID5 is the time taken to calculate the parity bit. When log files or data files are on a RAID 5 array, the write requests are obviously very high on a busy system. The time taken for the parity calculation is extra overhead that costs.
We've taken a RAID 5 setup and moved it to a RAID 1/0 and there was a marked increase in performance.
It's always a battle to stay away from RAID 5 because it is the cheapest solution. Bean counters struggle with the fact that you are about to buy 6 120GB SCSI drives but only able to use / see 3. Best option, don't divulge this information
If you can, if you have time, play with the different configs and see.
If you running a warehouse with historic data, put that on it's own read only FG on a RAID5 array.
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
-
RAID5 is not the way to go. See the below article on SQL Server Storage Best Paractices:
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/storage-top-10.mspx
-
we had a server we upgraded with 4 320GB HD's because they needed the space. RAID5. At first it was OK, but we bought a company and suddenly amount of data doubled. and the RAID became a huge bottleneck.
gnawing and gnashing of teeth and we bought a new server with a storage array and 14 HD's. 2 OS, 2 logs and the rest in a RAID 10 for databases. very nice performance
it's nice when you read data, but when you write you take a hit from the indexes and the RAID since it has to write the parity bit to recover from a bad drive.
RAID 10 is mirrors that are then striped. you lose half the drives like RAID1 but get the benefit of RAID 0 striping.
-
even with 4 disks you will get so much better performance with raid 10 then raid 5. It's really well documented about raid and databases, been in sql books since sql 7.0. Watch out for raid 6 which is even worse than raid 5.
Bean counters and san/storage vendors are partly to blame, the over inflated prices for some storage, I ask you £10k for a 500gb starter san from one company , that's £10,000 or $20,000 to have a fibre connection to a sata disk which costs maybe £50 ($100). Anyway your salesman sell you a 100tb san for x and says well yes the storage is x per dollar or pound and you can achieve 95% utilisation. Along comes DBA - I need raid 10, that's halved the capacity of the mega buck SAN, DBA says, I need dedicated spindles - suddenly your SAN is many many more times more expensive than DAS - so average dba get multiple shared luns on large raid 5 arrays and performance stinks! and everyone blames sql server. There is only one raid for databases and it's 1/10 unless you never do a write - this includes backup drives which also should be raid 10. raid 5 gives you 25% of the write performance of raid 10.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
You hit the nail on the head. What the salesman tells the bean counters and what is reality are two different things.
We had a rapidly growing, around 40-50GB a month, 8TB warehouse all running on RAID1/0. To get disks for it, bearing in mind we used 72GB 15k SCSI, was a nightmare. Always wanted us to "change the way we work" in order to be more cost effective.
The issue we had was the daily ETL, excluding month end, was taking around 4-5 hours. Month end, we would overrun our window of 8 hours. Any compromise in the disks would have nailed us.
Always an uphill battle...
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
-
First, I'll agree that RAID 10 is better performance, though I've seen some tests showing multiple RAID 1 arrays perform better. But that's nitpicking. If you can, get a few more drives and go with R1 for your logs and db and the blobs. If space is an issues, put the data and blobs together.
HOWEVER, I have used R5 in many, many databases and it's worked well. It performs well and it's been reliable. I've never lost data on a R5 array in over a decade. It's performance isn't as good as R10, but it isn't horrible. It works IF you are stuck with a budget that doesn't allow you to go to R10 and meet your space needs.
I will say I'd rather go with one large R10 array and combine logs and data if I had to.
-
I've seen a lot of talk about using different variations of RAID, and the pros and cons of x vs y. But, here's a different question:
WHEN a drive fails, what has to happen to fix it? My understanding is that you:
1. Identify the failed spindle
2. Replace the failed spindle
3. Reconstruct the failed spindle
4. Continue business as usual.
Can anyone point me to a resource where I can identify the next level of details below this? Obviously some of the steps are vendor specific (tools used to ID, how to replace, etc...) but in a generic fashion, there's a process that needs to be followed.
Second question: How do you know that a spindle fails?
Third question: In RAID 10, if one spindle has a sector failure, do you still have true mirroring with a rewrite to a different sector? At what point is the spindle considered 'in trouble' or 'failing'?
Please provide source citations for extended reading!
TIA
Steve
-
At a "here we are at 40000 feet" level, yes. Pull the drive out (It'll be the one with the flashing red light) and plug in the new one.
All raid software will have a monitoring utility which will alert you to a problem and allow you to rebuild the broken drive.
This goes for all trypes of raid (Except striping where your volume will be irriversably broken)
Tools and litricture are all different according to vendors. Your best best, google it. There will be plenty of good articles. Just a lot of reading
It is imepritive you replace a broken drive (especially in a RADI 5 array) as quickly as possible as you are now exposed. Any further failure and you will loose the array.
When a drive fails, by nature of a redundent array, the server will know no differnt.
When adding a drive, you will have a performance issue while the array rebuilds the broken drive.
I know of people who do not use the same batch run of drives in an array. The extra load on rebuilding the array could cause another failure. If you have two dodgy drives, the extra load could break the second one. When you talking hundreds of spindles, this is a possiblility...
Finally, any good raid controller will identify damaged sectors and not try use them again. As to ratio of dud sectors vs good before I drive is deemed faulty - I do not know. I would imagine this would be vendor specific. Also there should be errors in the event log about this.
Plenty reading to do and more questions...
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
-
Just remember that everything is a cost vs. benefit decision. It is really a matter of deciding where to spend the money, and that depends on your application. For example, spending money on memory or CPUs is often more cost effective for performance than money spent on disks.
A 10 disk RAID5 array may be faster than using the same 10 disks in a RAID 10 array, especially for read performance, since the data will be stripped across 10 spindles in the RAID5 array and across 5 spindles in the RAID10 array.
I ran benchmark tests using SQLIO on a system this week that had small RAID1 and RAID10 arrays that were substantially outperformed by RAID5 arrays in all types of IO: sequential/random read/write. I ran the same 16 tests on all disks on the system, and the RAID5 arrays were faster on every test, so there is more to it than just the RAID type. Number of disks, disk type and speed, controller type, amount of cache memory, and other factors all influence the IO performance. So if you have the money to buy 20 disks, you may get better performance with two 10 disk RAID5 arrays, than with the two 5x2 disk RAID10 arrays.
In the end, the real problem is getting the most performance for the amount of money you have to spend, within the other constraints you have to work with.
-
If you're going to compare raid 5 and raid 10 then you must compare to the number of available disks not changing the same number of disks, so 20 disks in a raid 5 should be compared to 36 disks in a raid 10 config ( i.e. you must compare on capacity )
A single disk suffers a 75% degredation on io performance on raid 5 compared to raid 10 ( or raid 1 ) for writes. For reads they are technically the same, other than many raid controllers can read from both sides of the mirror so reads from a raid 10 are usually twice as much as those from a raid 5. We're talking server hardware here not software raid and talking SCSI disks not sata , eide or ide. ( or fibre attached disks )
If you managed to get raid 5 to outperform raid 10 it either was a flawed test or a strange setup or not comparing like for like.
The failure risk for a raid 5 increases with every disk you add, for a raid 10 the risk is constant regardless of the number of pairs.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply