SQL Server Performance

RAID 10 questions

Discussion in 'Performance Tuning for Hardware Configurations' started by Murf, Jul 26, 2006.

  1. Murf New Member

    I will be setting up a hardware based RAID array on a new server and I
    have some questions regarding RAID level 10. Currently I am running
    hardware based RAID 5 on Windows 2000 server running MS SQL Server 2000.
    On my new server, I will be running Windows 2003 server/ MS SQL Server 2000.

    If I understand RAID 10 correctly, half your drives are striped like RAID 5
    and the other half of the drives mirror the first half.

    I am buying 6 hot-swap drives. For simplicity, lets say they are 10GB each.
    So the RAID 10 50% rule gets me to 30GB total space. (3 HDs * 10GB).

    My question is: Being these 3 drives are striped, do I have to now
    subtract an additional drive capacity out like I do for RAID 5?
    So I would end up with a total usable drive space of 20GB?

    Also in the case of a drive failure, how does it work?
    Currently in my RAID 5 if a drive fails everything runs a little slower
    but continues to run. I hot-swap the bad drive and the array rebuilds
    over the new drive and everything is back to normal.
    So in RAID 10 will it just switch over to the mirrored half that does not
    have the problem? Can I hot-swap the drive and will it rebuild and be
    back to normal like I do in RAID 5?

    Does RAID 10 give me an extra level of protection because I have stripping
    and mirroring? We had a bad stripe on our boot sector on our RAID 5 setup
    and the server would not boot up after a restart.
    Would RAID 10 prevent that from being an issue in the future? Is the OS
    and all files mirrored? Would it "know" not to mirror the bad stripe across?
    So would my mirrored drive be intact and continue to function?

    A lot of questions I know. Just trying to determine if I should stick with
    RAID 5 or if there are any benefits (and what they are) of RAID 10.

    Thanks to anyone who can help to answer my questions.

  2. waqar Member

  3. Murf New Member

    Thanks for the reply.

    I think the 2nd link is bad - I couldn't get it to open.
  4. joechang New Member

    regardless of raid performance theory
    actual hardware vendor implementations of raid have distinctive characteristics,
    sometimes significantly less than the theoretical limits

    always test your disk performance on a sufficiently large data set
  5. waqar Member

    Hi Murf,<br /><br />I am sorry link was working a few minutes ago <img src='/community/emoticons/emotion-1.gif' alt=':)' />.<br />FYR (taken from <a href='"http://www.experts-exchange.com/Storage/Q_20640972.html"' target='_blank' title='"http://www.experts-exchange.com/Storage/Q_20640972.html"'<a target="_blank" href=http://www.experts-exchange.com/Storage/Q_20640972.html>http://www.experts-exchange.com/Storage/Q_20640972.html</a></a>)<br /><br /><font color="blue">This is a nice little problem. I've never tried to prove mathematically that RAID 10 is better than RAID 5 but just ranted about it. It's not an easy thing to do since you need to make a few assumptions to simplify things a little.<br /><br />Assumptions:<br />1) RAID 5 writes take twice the time as a RAID 10 write. This is a fair assumption to make since it has twice the IO operations to do and calculate parity. Real world stats tend to back this up.<br />2) Seek time and rotational delay have no effect on the performance. This is obviously an over simplification but to keep things simple it needs to be made. If anything it will help out the RAID 5 stats since RAID 5 has a bigger problem with seek and rotational delay.<br />3) 2/3 reads and 1/3 writes.<br /><br />andyalder you are correct with the RAID 10 figures and we get an average of 117 IO/sec.<br /><br />RAID 5 gives us 130 IO/sec read, 65 IO/sec write giving an average of 108 IO/sec.<br /><br />Probability is not my strong point so feel free to correct anything. Given 14 disks the probability of a failure in any one day is 0.14. The probabiliy of two failures in any one day would be 0.0182 and the probability of 3 failures in any one day would be 0.002184.</font id="blue"><br /><br />But i will recommend to read JoeChang's articles and you will learn what you are looking for <img src='/community/emoticons/emotion-1.gif' alt=':)' />.<br /><br />Waqar.<br /><br />________________________________________________<br />~* Opinions are like a$$holes, everyone got one. *~
  6. Murf New Member

    Thanks for the info.
    Unfortunately I'm not a subscriber to the site your link points to so I cannot read the solution.

    I've been reading here:http://www.storagereview.com/guide2000/ref/hdd/perf/raid/concepts/rel.html

    My primary function is an App. Developer, but I also have to order the SQL servers and set them up. So I don't know quite the detail you are getting into.

    Can you tell me this. In RAID 10, if a drive fails what happens? Does the system keep on running and switch over to use the mirrored functioning equivalent drive (or set of drives)?

    Can more than 1 drive fail and system still run - say drive 1 fails in set 1 and drive 3 fails in set 2. Can RAID 10 use both sets to piece the whole thing together? OR can you take swap the defective drive from one set and put it into the other set to keep it running? (So in my example remove defective drive 1 from set 1 and pull out drive one from set 2 and plug it into the hole that defective drive 1 was setting in - in set 1.)
  7. waqar Member

    Hi Murf,<br /><br />I understand your situtation, i have gone through same process some time ago <img src='/community/emoticons/emotion-1.gif' alt=':)' />.<br />But no worries you are in good hands at this forum <img src='/community/emoticons/emotion-5.gif' alt=';)' />.<br /><br />You can have HotSpare for RAID-1,RAID-5 & RAID-10 (<a href='"http://en.wikipedia.org/wiki/Hot_spare"' target='_blank' title='"http://en.wikipedia.org/wiki/Hot_spare"'<a target="_blank" href=http://en.wikipedia.org/wiki/Hot_spare>http://en.wikipedia.org/wiki/Hot_spare</a></a>).<br /><br />1- RAID 5<br />Let say you have 3 Disks you will add 1 or more drives as Hotspare to be available when 1 oe more of your RAID drive will fail.<br /><br /><font color="blue">Total Disks = 4<br />Hot spare(s) = 1<br />Capacity = 200GB Each<br />Raw Storage = 800 GB<br />Usable Storage = 400 GB </font id="blue"><br /><br />2- RAID 10<br />Let say you have 6 Disks in RAID 10 then you will add 2 or more as Hotspare (RAID 10 require EVEN number in Hotspare) to be available when your RAID drive will fail.<br /><br /><font color="blue">Total Disks = 6<br />Hot spare(s) = 2<br />Capacity = 200GB Each<br />Raw Storage = 1200 GB<br />Usable Storage = 400 GB </font id="blue"><br /><br />Hope this will help.<br />But before you buy a server, please check Joe's recommended configuration.<br />Just curious, How much budget you have for your DB server (is it low budget)?<br /><br />Waqar.<br /><br /><br /><br /><br /><br />________________________________________________<br />~* Opinions are like a$$holes, everyone got one. *~
  8. Murf New Member

    Thanks.

    I have the server already speced out (IBM). I'm am just trying to decide how the GBs of hard drives based on the (hardware) RAID I'm going to use. There are going to be 6 drives - either 73GB or 146GB.

    I'm still uncertain whether to stick with RAID 5 or change to RAID 10 on new server.
    Budget is around $9,000. I don't want to degrade perfomance going to RAID 10, but want to retain the same or better fault tolerence. (Currently have 5 drives in RAID 5 plus a hot swap spare - RAID 10 would be putting down to 3 drives. So would the less amount of drives degrade performance from what I have now?)

    Also, could you answer the questions in my previous post on what actually happens when a drive fails in a RAID 10 array?

    Thanks
  9. waqar Member

    Hi Murf,

    1-
    RAID 10 only works on EVEN Drives. You cannot use ODD number of (3,5,...,2xn+1) drives. For 5 drives you can only use 4 to Built RAID 10 and if you want Hotspare you will need 2 more drives (1 will not work for RAID 10).
    So this also answer your question that YES, you can use Hotspare in RAID 10 but in even number (2,4..,2xn).

    2-
    You are using current system for write intensive or read intensive purpose?

    3-
    What total usable storage capacity you are looking at (what is your current DB size)?

    4-
    RAID 5 is good if you are not doing 1000 of transaction every hour minute. If your transaction level is to high, and you are using RAID 5 then you will suffer.

    5-
    Off-course more drives -> more head -> better performance.

    Waqar.

    ________________________________________________
    ~* Opinions are like a$$holes, everyone got one. *~
  10. Murf New Member

    1-<br />I do now and will in new server have 6 drives (73Gb or 146gb). So the total number of drives is 6 (it is ok to have odd number on each side correct? (like 3 and 3)).<br /><br />2-<br />Microsoft SQL Server 2000 is all that runs on it. So I would say intense both read and write.<br /><br />3-<br />Total space used by SQL server = 36Gb. Total HD usable space 144Gb.<br /><br />4-<br />The response now is good I would say. The only reason I am question if I should go with RAID 10 is if performance is same or better, that is ok, <u>BUT the MAIN reason is if the Fault Tolerence is better/more reliable than RAID 5</u> (Have had a bad stripe on boot sector before - caused major problems). <img src='/community/emoticons/emotion-6.gif' alt=':(' /><br /><br /><b>But how do I know if going FROM RAID 5 with 5 usable drives (36gb each) TO RAID 10 with 6 drives (but only 3 usable - 73Gb or 146GB) will result in slower performance?</b>
  11. merrillaldrich New Member

    One key to Raid 10 fault tolerance is whether the raid is 1+0 or 0+1 (Mirror of striped drives, or stripe across mirrored drives) and whether your vendor can supply the latter. The best redundancy is to have pairs of mirrored drives which are collected together -- because if more than one drive fails, the odds are better that the system might stay up. Other way around, if two fail then it's more probable that you are dead in the water.
  12. waqar Member

    Hi Murf,

    1-
    As long as all Disks are under SAME CHANNELS it is ok (orientation doesn't matter).

    2-
    So it is a dedicated server. But being dedicated doesn't mean READ/WRITE Intensive.
    If you are using this server for Accounting Application then i can say it is READ/WRITE Intensive.
    If you are using this server for Reporting then i cay say it could be READ intensive.

    3-
    You have to calculate
    If you are using 76GB disks

    RAID 10
    (with HotSpare)
    Total Disks = 6 Disk
    Hotspare = 2 Disk
    Total Raw Space = 438GB
    Total Usable Space = 146GB

    (without HotSpare)
    Total Disks = 6 Disk
    Hotspare = 0 Disk
    Total Raw Space = 438GB
    Total Usable Space = 219GB

    RAID 5
    (with HotSpare)
    Total Disks = 6 Disk
    Hotspare = 1 Disk
    Total Raw Space = 438GB
    Total Usable Space = 292GB

    (without HotSpare)
    Total Disks = 6 Disk
    Hotspare = 0 Disk
    Total Raw Space = 438GB
    Total Usable Space = 365GB

    I think even you go for RAID 10 it will be good enough to hold your data. But you need to calculate your future growth.

    4-
    You also have to plan where you are going to put your log file? Keeping log file at separate RAID 1 will help in performance.

    5-
    To my knowledge purpose to Hotspare is to take over when 1 or more disk fail. Regardless of boot sector, in your previous configuration you have Hotspare?

    6-
    I don't think RAID 10 is more stable than RAID 5. Both have its own pros/cons, which you need to decide you want to sacrifice WRITE performance at expense of SPACE. But again base on application you need to check what is WRITE frequency.

    Waqar.





    ________________________________________________
    ~* Opinions are like a$$holes, everyone got one. *~

Share This Page