Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am rather new to databases. I was reading up on RAID Storage and it seems that the consensus is that RAID 10 is

RAID 10 is best is terms of performance and redundancy

However at the end article, the author went on to state

Indepedent pairs of RAID 1 is superior to RAID 10 provided application knows how to evenly distribute data across multiple volumes

The author George Ou did an analysis to proof why Indepedent pairs of RAID 1 is superior to RAID 10 though I can't really understand as I am quite new to databases

However another author: Robin did his own analysis and rebuts George Ou analysis.

I am confused over all these analysis which is totally out of my depth

These are my following questions

  1. Is it really true that independent pairs of RAID 1 is superior to RAID 10 provided application knows how to evenly distribute data across multiple volumes?

  2. In practise, it is easy to create an application that is able to even distribute data across multiple volumes and how is it done?

  3. Can someone provide a simplified explanation regarding the above 2 points?


migration rejected from Jan 1 '14 at 21:02

This question came from our site for professional and enthusiast programmers. Votes, comments, and answers are locked due to the question being closed here, but it may be eligible for editing and reopening on the site where it originated.

closed as primarily opinion-based by Mark Storey-Smith, RolandoMySQLDBA, Michael - sqlbot, Kin, Max Vernon Jan 1 '14 at 21:02

Many good questions generate some degree of opinion based on expert experience, but answers to this question will tend to be almost entirely based on opinions, rather than facts, references, or specific expertise.If this question can be reworded to fit the rules in the help center, please edit the question.

In my opinion both articles are of remarkably poor quality, demonstrate a limited understanding of the topic and employ shoddy test methods. Ignore. – Mark Storey-Smith Dec 30 '13 at 12:26
@MarkStorey-Smith, I agree! – Edward Dortland Dec 30 '13 at 19:09

The George Ou article doesn't appear to describe the database configuration (inside MS SQL) relating to the number of devices the database is using.

From what I can gather he changed from one device (the raid 10 array) to multiple devices for the database and that should provide much better response in MS SQL 2000 (probably later versions too) and Sybase (at one time the same product) as more devices allow multiple IO's to be performed simultaneously rather than putting everything through one configured device. Hence the low CPU numbers as IO is usually CPU intensive, but if you are throwing everything down only one device then you aren't going to tax the CPU.

His bottleneck wasn't RAID 10 but the number of devices presented/configured inside MS SQL. Bottom line: he finally used SQL Server as it was designed to be used i.e. multiple devices per database if you want maximum throughput.

So to finally answer your questions:

  1. no

  2. in practice, no it is not easy; that is why most people use databases. The big issue is using the DBMS of choice incorrectly which then leads to the wild and crazy ideas that it is better to spend time and effort duplicating the DBMS software manually in the application. While I concede there may be times that is appropriate, the bottom line usually comes up with different answer :-)

  3. I hope the preceding paragraphs have explained it. Apologies if it has not.

Although there is a slight performance benefit from using multiple files (Paul Randal has done a test on this, but I can’t find right now, it’s at I believe it's incorrect that a single file only provides serial IO handling. All threads are able to perform IO actions and they can do it in parallel regardless of the amount of files. – Edward Dortland Dec 29 '13 at 18:47
yes Edward you are correct. I did not mean to imply it was serial IO, just that only using 1 device/file will cause a bottleneck within SQL Server as all IO is being handled by one spin lock (I think that is the correct term). so only one IO can be processed at a time by the database engine even though several IO requests may be outstanding. – Mark Dec 30 '13 at 16:44
"so only one IO can be processed at a time by the database engine even though several IO requests may be outstanding" This is incorrect. SQL Server IO handling is asynchronous. So any worker can issue a IO request. This can all be in parallel. The amount of files has no relation with the amount of outstanding IOs at any given time. A scenario where there is a relation between files and parallel IO threads is when you create a database. SQL Server will use a thread for every data file that is on a unique drive letter (pre SQL 2005) or on a unique volume (SQL 2005 and higher). – Edward Dortland Dec 30 '13 at 19:03

Logically there is no difference between letting your RAID controller stripe data across multiple mirror pairs or doing it your self.

A RAID 10 config of 4 disks that each can do 180 IOPS has 4 x 180 IOPS read capacity and 2 x 180 IOPS write capacity.

A 2 times RAID 1 config has exactly the same IOPS capacity. Only difference is that instead of the controller distributing the data across the different mirror sets you are now doing it yourself.

However, I can imagine that their could be a difference in performance caused by non equal data distribution or non equal data access.

For Example, If I take a MS SQL dabase and I great a 100MB file on RAID1 volume A and a 1000MB file on RAID 1 volume B and then create table across those files. When I'm inserting records, I'll be inserting 10 times more records on volume B than on volume A. (this is because of the proportional fill algoritm of MS SQL). Once I reaching the IOPS capacity of volume B, I'm only using 10% of the capacity of volume A.

If you choose RAID 10, your controller, or in the case of George OU the RAID software will all take care of this even distribution of data across disks for you. Your less likely to have distribution problems then when you try and do it manually.

Why is George believing that seperate RAID1 volumes outperform RAID10?

reading this quote:

I had to shift the results for 2xRAID1 to the right by a factor of 2 and shift the "four drives" result to the right by a factor of 4. This is to account for the fact that I had 2 and 4 times more threads (more IOMeter workers) than the other single volume results, which is the equivalent of 2 and 4 times more depth on the outstanding queue.

It shows that he used double the amount of threads to generate IO load on the "2 seperate RAID1 volumes" test compaired to the "single RAID 10 volume" test.

He incorrectly assumes that he can correct that by moving the graph to the right on the x axis. The x axis is queue depth.


Not the answer you're looking for? Browse other questions tagged or ask your own question.