Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase ««123»»

RAID 5 and SQL Server Expand / Collapse
Posted Monday, January 10, 2011 3:31 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, September 4, 2015 2:48 AM
Points: 562, Visits: 1,039
At a previous company where we used internal server disks, instead of SANs, we used an interesting mix of RAIDs for the different drives. And as usual the answer is "it depends"...For our systems that were massively critical and performance orientated, we used RAID10. For our backup, archive and reporting systems we used RAID5. Now I am onto SANs and hardly get to see/touch/change anything on them.

It was said earlier though, SQL was (is) a cost effective solution for databases, when compared to Oracle and the hardware seems to fall inline with that. Would be interesting to Oracle and SQL next to each other on the same hardware doing the same tasks
Post #1045196
Posted Monday, January 10, 2011 6:05 AM


Group: General Forum Members
Last Login: Thursday, June 26, 2014 8:53 AM
Points: 143, Visits: 58
We asked our centralized IT server support organization about using an SSD but they will not support them for the time being.
Post #1045248
Posted Monday, January 10, 2011 6:54 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 2, 2011 2:46 AM
Points: 1, Visits: 3
Penalties in writing on RAID: during the nineties an issue on server hardware, 2011 an issue on gamer PC.

In former times it was the calculation and the limited processing speed of hardware raid controllers leading in reduced data transfer rates. E.g. Adaptec AAA2400 has 4x UDMA 33 channels and supports natively Raid5.

In stripeset mode on 4 discs the controller writes about 126 MB/sec, that's the limit for PCI 32 bit 33 Mhz. In Raid 5 mode the CPU on the controller (Intel I960) gets really hot, and it writes with about 30 MB/sec.

This penalty is more or less gone, at least if customers don't buy their servers at Walmart having an ICH10R built in.

Current quality hardware is able to calculate redundancy in realtime. Having 4 disks a simple exor algorithm can be used, but ICH10R is much faster running on odd disk numbers. That is cheap scrap, and an admin will die in hell when he uses ICH10R for productional use.

I can also understand the Oracle guys living with a dozen independant disks in their system, but forgetting that this is fault multiplying and not fault tolerating.

Both strategies (Raid and indiv. disks) are able for offline recovery if hardware fails, and Raid5 on cheap hardware is nearly intolerable.

In general I would talk about reliability and not about "I like / I dislike Raid". Enterprise storage has to be reliable, this is a fact. It has no flaws during a rebuild and just works.

Spending 100.000 $ for an enterprise SAN doesn't leave gaps, is fast enough so we're discussing about small companies and 1000$-5000$ entry class servers, barely worth being called servers.

Anyway, a good Raid controller should NOT show any kind of penalties nowadays, nor even or odd disk number affinities.

In principle raid 5 with n+1 layout can die easily if a disk fails and during the rebuild the next disk fails. Raid 5 n+2 could be a better (and more expensive) solution, but concerning smaller systems having only 4 disks there are 2 idle disks in Raid 5 but also in Raid 10 setup. Raid 10 will die later in this config, but the risk of failing disks is highly increased by just buying a bulk of disks from the same producitonal date.

Post #1045269
Posted Monday, January 10, 2011 7:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 24, 2012 4:46 PM
Points: 1, Visits: 10
Since I work with the largest SQL Server implementations in the world, it is essential that RAID 5 is removed from the picture, even for read mostly databases. I am a huge proponent of the "kill RAID 5 for databases" movement.

RAID 10 is still the best general recommendation for databases, with RAID 1 being a good recommendation for some data warehouses ( see the Fast Track Implementation Guide). Those DBAs implementing RAID 1 tend to be more advanced and know their application well enough to balance the disk IO over multiple database files and multiple LUNS themselves.

One thing that most people miss is that many of the newer disk controllers/HBAs will do dual reads off the mirror. So even read mostly databases will get a big benefit from implementing RAID 10. Check your cards to see if they have this capability, even in the SANs. An interesting side note is that the lower cost SANs were among the first to include these type of cards.

As far as SSDs go, they are the great equalizer in the random vs sequential IO discussion and really have nothing to do with RAID 5 vs RAID 1/10. You still pay the write penalty with RAID 5. Having said this, you can probably get some relief if you are having IO waits in a RAID 5 implementation with SSD just because it is faster. But at some point if you grow big enough you will end up killing RAID 5 anyway.

And the discussion about disk caching helping RAID 5, what you are really saying is that it is helping up to a point. In the large systems I work with, write caching is practically worthless and is generally turned off because the volume of writes will overrun the cache and the system ends up waiting to get its turn to write to the cache. This holds true for any RAID implementation.

In reality, SSD + write caching will help in the small to mid size RAID 5 implementations. But if you know you are going to experience significant growth over the next few months/years, go ahead and start moving to a mirrored RAID solution.

I would like to close with the statement that those using RAID 5 don't get the right to complain their DBMS doesn't scale.

Kevin Cox - SQL CAT
Post #1045274
Posted Monday, January 10, 2011 7:37 AM


Group: General Forum Members
Last Login: Monday, December 8, 2014 7:53 AM
Points: 107, Visits: 52
I always prefer to consider facts rather than rely completely on theory. Some years back I conducted a test of SAN RAID 5 disk arrays vs. RAID 10 arrays, for various sizes and combinations or read and write activity. I found that as disk size increased, the performance of the RAID 5 arrays caught up to the RAID 10 arrays, and by the team I reached 512Gb sized disks, the RAID 5 array was actually faster. Unfortunately I no longer have the results, but in any case your mileage may vary depending on what hardware you are using. If you have a SAN and you have the flexibility to try both options, I would suggest that you try a direct comparison by using the SQLIO benchmark tool and also try timing some simple SQL server operations (e.g. backup).
Post #1045303
Posted Monday, January 10, 2011 7:59 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, February 24, 2015 3:51 AM
Points: 25, Visits: 180
In debating RAID 5 against RAID 10 performance for SQL we need, in my view, to distinguish between read and write performance

For read performance:

SQL read page requests (except for SQL read-ahead) are generally un-overlapped; your request waits until the read is satisfied and your request can continue, you need to retrieve the top index level before you retrieve the next index level, before you retrieve the leaf index level, before you retrieve the data row. In effect, poor read performance mushrooms. In partial mitigation of this, metadata and non-leaf index pages have a relatively higher hit rate than index leaf and data pages, they tend to stay buffer or cache resident longer

Raid 10 may satisfy read requests faster than RAID 5; a competent RAID 10 implementation will allow access to the first available mirror copy. In effect, path contention can be halved by RAID 10, or it can be seen as doubling the effective device bandwidth. In a multi-user SQL environment this can be very significant. In some SAN environments path contention (rather than device contention) may be the major bottleneck, and the benefit of RAID 10 above RAID 5 may not be seen because of this constraint

A large device cache may, or may not, be an advantage to read performance. In most of the cached devices I
have worked with recently, including SANs, the SQL buffer pool has been significantly larger than the device cache. In effect, any SQL buffer miss is almost by definition also a device cache miss (both use similar ageing algorithms).

In my (biased) view additional RAM allocated to SQL buffer pools, rather than a larger device cache, is far more effective until the device cache is multiple times larger than the SQL buffer pool. A SQL buffer hit may be more like 10000 times faster than a physical read, a device cache hit may be less than a 100 times faster than a physical read under load (depending on implementation and path contention)

For write performance:

Data page write requests are effectively overlapped. I can find no specific reason why writing of dirty data pages during checkpoint should delay transaction processing, except if the flushing of pages is sufficiently slow as to
affect the free page replenishment algorithm. In effect, if data page write is sufficiently slow, it may cause free list stalls, with all the negative performance implications thereof. To the SQL heavies: if I have missed some aspects of data page write, please let me know

Log page write requests are not overlapped; an implicit or explicit transaction is not complete until the log pages reflecting that request are confirmed as written. The effect of poor write performance should not be cumulative, as it is in read performance; log write may require multiple physical writes, but they can be viewed essentially as either a single request, or multiple independent requests

RAID 5 page writes require cache hits (not buffer hits) on the page to be written, and the parity page, and possibly all the pages in the parity set (there are some implementations that verify the parity before write). In mitigation, the page(s) can be retrieved in parallel, but the physical write is delayed by the retrieval process. It is likely that SQL log page writes have a very low cache hit rate (log writes into empty pages rather than previously used pages), the retrieval of the parity page, and possibly parity set, is what delays the physical write. The same delay of physical write is not true for RAID 10 implementations

RAID 5 page writes require the page and the parity page to be physically written. In some implementations the writes are not strictly in parallel; recovery from failed writes requires a particular sequence of writes. Essentially the same is true for RAID 10 implementations

In all SQL-supported implementations the modified page and parity page (where applicable) must be stored in non-volatile cache before the write can be posted as complete; regardless of where they were retrieved from. In almost all implementations I have come across the non-volatile cache is significantly smaller than the volatile cache. In most implementations the size of the non-volatile cache is not readily increased. I have yet to meet the non-volatile cache that is not frequently (constantly ?) overrun under load, thus delaying write completion until space is available in the non-volatile cache. In effect, for SQL, write buffering reduces the write time, but in practice it is still significantly dependent on the physical write time of the underlying pages

What distinguishes RAID 5 write from RAID 10 write is that generally two entries are required in the non-volatile cache for RAID 5 as against a single entry for RAID 10, thus effectively doubling the size of the non-volatile cache, and thus reducing contention for space in that non-volatile cache. The non-volatile cache is in almost all RAID implementations a severely constrained resource. In addition, RAID 5 SQL log write time is significantly delayed by the retrieval of the parity page, which for SQL log is seldom a device cache hit almost regardless of the device cache size. This may significantly compound the contention for non-volatile cache space

Thus, in my view, a large SQL buffer pool, a RAID 10 implementation, and as large a non-volatile cache as I can get are very desirable

In my view SSDs do not change this equation, except that physical write rates to SSDs are significantly greater than to traditional disks. However PCIe-attached SSDs can be seen as a very large but relatively slow non-volatile device cache, which can benefit both read and write

Post #1045321
Posted Monday, January 10, 2011 8:45 AM


Group: General Forum Members
Last Login: Thursday, March 24, 2016 11:27 AM
Points: 16, Visits: 45
I have worked with medium to large databases in SQL Server and Oracle for some time and have seen just about any combination of disks you can imagine for them. When our financial system went from RAID 5 to RAID 10, the disk queues shank 40% and IO improved more than imaginable. We got to be heroes once the move was finished. At the time it was an IO intensive 50GB database with about 800 users.
Our POS system, which is Microsoft front to back, was crippled by an oversized RAID 5 array. We switched it to a RAID 10 array and did nothing else. The fix got reports from a 30 hour run to a six minute run. When we redistributed the files to improve the IOPS things got even better. Nothing changed but the disk configuration. The system works now like it should.

As hardware has gotten better the gap has narrowed some. The question you have to ask is, "How important is my data?" I want our people to get their data instantly every time. I don't want anyone to have to wait for information they need to make our organization work.
Post #1045371
Posted Monday, January 10, 2011 10:32 AM

SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, March 4, 2014 2:10 PM
Points: 35, Visits: 151
I may get my official membership in BAARF revoked, but in Oracle SQL tests in spring '09 with a then-new midrange IBM POWER6 server and DS-series SANs connected by dual 4GB FC HBAs, I saw no discernable IO performance difference in read or write between RAID-5 and RAID-10 setups, even under unrealistically stressed conditions (tests were throttled solely by CPUs ability to generate enough IO). I forget the exact array sizes for each, but the SAN caching was truly effective in nullifying the write performance penalty. I should also mention that we have an SVC frontending the SANs, although I'm not sure how that would affect IO performance for the testing, unless it's cache is additively effective (when it's not shuffling volumes around).

One other knock on R5 is the array rebuilding performance hit after a lost drive is replaced. We've lost multiple drives since then (guessing due to thermal stress after AC failure), albeit no more than one at a time in any array, and I've seen zero performance effect by either failure or replacement, at least on our production Oracle box.

When relaying this to my former boss who is BAARFier than I am, he said that they came to the same damnable conclusion. I guess technology really does change.

That being said, there is ZERO chance I'll approve of a standalone RAID-3/4/5/6/etc config for critical production systems. It's just the BAARF in me...
Post #1045444
Posted Thursday, January 13, 2011 8:41 AM



Group: General Forum Members
Last Login: Monday, November 21, 2016 11:03 AM
Points: 20,009, Visits: 18,255
The popularity, I think, of RAID is derived from installation types around the world and the people that are setting up these new databases. Mom and Pop shops may not have the money to buy RAID 10 and RAID 5 may be pressing their budget but it is the best option for them. It also depends on type of database and volume of transactions as to whether it is an appropriate choice. Not every database needs to be on RAID 10 - it just doesn't make sense for the cost versus transaction volume in many cases.

Jason AKA CirqueDeSQLeil
I have given a name to my pain...


Posting Performance Based Questions - Gail Shaw
Post #1047347
Posted Thursday, January 13, 2011 9:17 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 9:58 AM
Points: 3,243, Visits: 2,782
Having read all of the posts to this point, I find it interesting the arguments for/against one RAID vs another.

In my opinion and experinece the RAID rule of thumb has been data segments went on RAID 5 and T-Logs on RAID 1 or if able 10. In the case of SAN's, there was a lot more creativity but essentially the same.

That being said, I feel that one should also consider the type of database, whether OLAP or OLTP as well as the size and number of users.

In the case of OLTP you can place the data segment(s) on RAID 5, which will allow better read capabilities for those who are doing some reporting while the write protion (T-Log) is on RAID 10. This RAID is overall better for writing data. Then, as all know when a chackpoint occurs the data is written to the database files without much of an impact to the data-entry side.

In larger or even busier databases, it may be of benefit to partition the database itself into seperate files placing these files onto and appropriate RAID array. ie Indexes for a certain table to a seperate file group on a seperate RIAD 5 array while the data for that table may be on it's own RAID 5 array.

I personally like RAID 10 for my T-Logs as this array is actually more conducive to faster writing speeds.

On larger databases, I also try to ensure that at least my RAID 5 and 10 is on a seperate channel on the controller, if not on seperate controllers all together.

I also make sure that I have my tempdb on yet another RAID10 array in order to ensure that there is no contention there as well as on seperate controllers (most of my tempdb's ate approximately 100GB + and heavily used). I have found in several cases a need to partition these into seperate files as well and mve them to seperate drives.

I also have a few small databases all on RAID 10.

So I guess I would say that it isn't as simple as saying RAID 1 or 5 or 10 or 50 or ...

Now IO extremes, such as reducing reports from 30 hrs to minutes - I suspect that there is a lot more there than a simple move of 1 database or database file.

Steve Jimmo
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
Post #1047382
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse