RAID and Its impact on your SQL performance

  • Nadrek

    SSC-Insane

    Points: 20039

    tony.turner (5/2/2012)


    Sounds suspiciously like your hosting company has been listening to their EMC salesperson. EMC apparently is strongly in favour of RAID 5 for SSD, thus the loss of a single disk in the array. The EMC claim is that RAID 5 on SSD has a minimal write latency effect. Not convinced from measurements in practice

    My own tests of locally attached 6 drive (enterprise) SATA SSD's vs. 10 drive 15k FC SAN disks shows that the 6 disk local SATA RAID 5 is always faster; even for 8KB random writes the 6 SSD RAID 5 is near triple the performance of the 10 disk RAID 10, though for 64KB random writes it's only about a third better. For most other categories the SSD RAID 5 is very much faster, though I was limited to 4Gbps throughput on the SAN in my tests, which is an artificial throughput ceiling.

    SQLIO, at least, also shows average and max latencies to be much better for the local SSD's than for the SAN.

  • rbarbati

    Valued Member

    Points: 57

    This is a nice article, but possibly a bit dated and not really considering higher-end enterprise-scale subsystems that are available and more common these days, where RAID is not only obsolete, but not even a configuration option on the subsystem. Higher end arrays are now 'wide-striping' (as one term) across the entire array and are managed by the subsystem HW/FW/SW itself. Configuration is limited more to logical units and volumes. So although the discussion is valid for a more traditional physical server, I beleive more consideration needs to be placed on newer generation virtual compute modules with high-end disk subsystems. Also when reaching multi-terabyte/pedabyte scales, spindle counts are not much of a factor really either, each spindle possibly (typically) being a terabyte drive in itself. These systems are tuned and configured so differently than, say a local disk array, that I feel it warrants mention.

    I'm finding many of the articles I recieve from SSCC are not considering true enterprise and large scale systems, focusing more on simpler stand-alone systems and may be misleading to newer adminstrators who might be working in enterprise level environements.

    I'd love to start seeing more write-ups targeting higher profile systems, as that is where the leading edge of technology lives and where the real sticky intracacies tart getting interesting.

    The RAID topics are great above, dont get me wrong, but it's just reminiscent of a era past.

    But that's just one humble adminstrators opinion of course... it's always good to see folks writing and sharing knowledge of any kind, so keep up the work !

  • Nadrek

    SSC-Insane

    Points: 20039

    rbarbati (5/2/2012)


    This is a nice article, but possibly a bit dated and not really considering higher-end enterprise-scale subsystems that are available and more common these days, where RAID is not only obsolete, but not even a configuration option on the subsystem. Higher end arrays are now 'wide-striping' (as one term) across the entire array and are managed by the subsystem HW/FW/SW itself. Configuration is limited more to logical units and volumes. So although the discussion is valid for a more traditional physical server, I beleive more consideration needs to be placed on newer generation virtual compute modules with high-end disk subsystems. Also when reaching multi-terabyte/pedabyte scales, spindle counts are not much of a factor really either, each spindle possibly (typically) being a terabyte drive in itself. These systems are tuned and configured so differently than, say a local disk array, that I feel it warrants mention.

    I suspect one of us needs to study "wide striping" in greater depth. As far as I'm aware based on a cursory inspection of the literature, "wide striping" is commonly using the very normal, standard RAID levels this article and discussion references, but with more drives. I.e. instead of a minimum 2+1 RAID 5 array, or even a normal 4+1 or 5+1 RAID 5 array, it's implementing an N+1 array, where N is "large". It's got all the normal features of a RAID 5 with many disks in the set; it averages out performance, it's got the write penalty, and it is resilient to only 1 drive failure at a time. When you put multiple workloads on the array, you get all the same features: when both Workload A and Workload B run at the same time, they compete for resources (and if everyone runs really big, long operations the first day of the quarter, perhaps they all take a lot longer), but when only one workload is active, it gets all the performance.

    Reference: http://www.storagerap.com/2010/03/calculating-the-output-of-wide-striping.html

    Reference: http://gestaltit.com/all/tech/storage/chris/enterprise-computing-the-wide-striping-debate/[/url]

  • mrpellepelle

    SSC Veteran

    Points: 274

    GregoryAJackson (5/2/2012)


    Hi

    do you think it would be a big performance loss to store tempDB data and Logs on the same Raid?

    in our BI(OLAP) enviroment , we use

    1. RAID 10 for Data1 and Log2

    2. RAID 10 for Data2 and Log1

    3. RAID 10 for TempDB Data and Logs

    You will benefit from seperating TempDB and Logs.

    Budget is always an issue so you gotta pick your battles. But in a perfect World, Id absolutely seperate them.

    GAJ

    Hi

    Just to be sure you get me right:

    3rd RAID ist only for TempDB Logs.

    This RAID is build of 10x 15k 600GB 3,5" Hdds.

    Would you go with this config, or would you recommend to build 2 Raids out of the 10 available HDDs?

  • jahid786

    SSC Enthusiast

    Points: 185

    How can i check my disks RAID status? I mean , how can i become confirm that my disks are on RAID 10?

  • GregoryAJackson

    SSCrazy

    Points: 2794

    Typically the RAID Controller will have an administrative software application or control panel.

    this is where you can see the RAID config settings and make modifications if necessary

    GAJ

    Gregory A Jackson MBA, CSM

  • Perry Whittle

    SSC Guru

    Points: 233859

    GregoryAJackson (5/4/2012)


    Typically the RAID Controller will have an administrative software application or control panel.

    Ultimately it's the BIOS on the RAID controller that handles the array(s), for vendor specific you may have a management interface without rebooting and entering the BIOS

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • sqlquery-101401

    SSCarpal Tunnel

    Points: 4951

    what is the best way to get the production IOPs requirement , we have ETL that runs for an hour and trying to get new H/w , what is the best way to know the IOPs requirement with current production load, will disk transfer/s will suffice or any better way?

  • GregoryAJackson

    SSCrazy

    Points: 2794

    sqlquery-101401 (5/7/2012)


    what is the best way to get the production IOPs requirement , we have ETL that runs for an hour and trying to get new H/w , what is the best way to know the IOPs requirement with current production load, will disk transfer/s will suffice or any better way?

    that is what I used.

    If your in a SAN\NAS environment there will be additional utilities that capture this info as well.

    GAJ

    Gregory A Jackson MBA, CSM

  • sqlquery-101401

    SSCarpal Tunnel

    Points: 4951

    Thanks , Yes i have couple of servers on SAN , what other utilities you refer?

  • GregoryAJackson

    SSCrazy

    Points: 2794

    sqlquery-101401 (5/7/2012)


    Thanks , Yes i have couple of servers on SAN , what other utilities you refer?

    I have never administred a SAN\NAS so i've always had to go to our IT Team or the Systems Engineers to obtain this data.

    GAJ

    Gregory A Jackson MBA, CSM

  • paul.knibbs

    SSCoach

    Points: 15270

    I'm a little confused. Surely the Disk Transfers/second counter in Performance Monitor will already be limited by the hard disk subsystem you have installed, so you can't use it to determine if you need to upgrade or not? :ermm:

  • yazalpizar_

    Ten Centuries

    Points: 1365

    Steve Jones - SSC Editor (5/1/2012)


    yazalpizar_ (5/1/2012)


    With the proliferation of SSD disk and its costs going down, some RAID levels with low write performances are no longer so low. Put an SSD in your life. I've done it, both home, laptop and work. Is the best money can buy right now in order to increase performance several degrees. We are planning to do it also on our local servers, first on the test server and then on the main one if evertyhing is ok.

    Make sure you have good backups and be careful here. When SSDs die, they die ,and you may or may not get good notice.

    A quick read: http://www.codinghorror.com/blog/2011/05/the-hot-crazy-solid-state-drive-scale.html

    😀 Very good article that one on CodingHorror. Sorry for not replying, was no aware of the thread and it has grown a lot with useful information. I indeed have backups of everything on external hard drive. At the office we finally didn't change the HD on the servers, is not a priority for our manager for now.

    PS: failed the MCITP sql server 2008 exam yesterday... :sick: need to work harder on it

  • Brian Souder

    Old Hand

    Points: 316

    Great Article - very helpful. Especially calculating the IOPS.

  • Toby Harman

    SSCarpal Tunnel

    Points: 4137

    Good article and some excellent comments

    Not much in here on RAID 50, which is rapidly becoming one of my favourite RAID levels for Data and Backups

    Logs and Tempdb are definitely destined for RAID 10 (1+0, not 0+1 as has been pointed out), but RAID50 gets around a lot of the write penalty associated with RAID5 by spreading the writes out across multiple arrays.

    As an example, if you have a 5 disk RAID5 array which can handle 100 IOPS, then striping this with an identical RAID5 array gives you 200 IOPS (in a perfect world, realistically probably only 190!).

    One area which isn't discussed in here, and can have an enormous impact on RAID performance is stripe size. Ensuring that your stripe sizes are multiples of each other as you stack the RAID levels is crucial to maintaining performance. Ensuring that the OS disk cluster size is a multiple will also help things along nicely.

Viewing 15 posts - 61 through 75 (of 96 total)

You must be logged in to reply to this topic. Login to reply