mibuso.com
Home› SQL Performance
Options

SAN and disk virtualization - does it really work ?

Slawek_GuzekSlawek_Guzek Member Posts: 1,690
edited 2008-06-05 in SQL Performance
Hi All,

I've got recently quite nice SAN in my hand and tried to configure it for SQL. The SAN is equipped with 24xSATA 500 GB disk. I had many chats with vendor's technical support guys. They always tried to convince me - create just one big array across all 24 disks, and then create two separate volumes as this allows SAN to perform at full speed and this is the fastest config, because every LUNs will use all of 24 disks so the performance will be the best.

The above technique is called 'disk virtualization'. Does it work in SQL environment as vendor claims ? Is this really the best config for SQL ?

I've run many different tests in many different configurations. The purpose was to check how the SAN performs under different RAID configurations and how the workload resembling SQL server disk activities influences overall SAN performance. There you have some of the results:

config A: (DATA and LOG LUNs are on the same disk array)
DATA : RAID r10, 8x500GB, 64kB stripe
LOG: RAID r10, 8x500GB, 64kB stripe

DATA LUN performance under no load: 
ACCESS type               sequential        random 
                        IOPS     MBPS     IOPS    MBPS 
  8 kb block  read   16667,84  130,21    108,4    0,84 
 16 kb block  read   14194,48  221,78    136,88   2,13
 64 kb block  read   6084,78   380,29    126,21   7,88
128 kb block  read   3462,26   432,78    106,25  13,28
256 kb block  read   1990,75   497,68     93,50  23,37
512 kb block  read   1046,59   523,29     82,28  41,14
DATA LUN Performance under load: (the load is 8 kb block sequential write to LOG LUN) 
ACCESS type               sequential        random 
                        IOPS     MBPS     IOPS    MBPS 
 8 kb block  read    11518,00   89,98    53,01    0,41
 16 kb block  read   11039,70  172,49    52,04    0,81
 64 kb block  read    4155,71  259,73    43,22    2,70
 128 kb block  read   2123,13  265,39    39,90    4,98
 256 kb block  read    889,84  222,46    28,04    7,01
 512 kb block  read    484,79  242,39    31,77   15,88

Now results when DATA and LOG are on separate disk arrays:

config B: (DATA and LOG are each on own separate disk array)
DATA: RAID r10, 16x500GB, 64kB stripe
LOG: RAID r10, 8x500GB, 64kB stripe

DATA LUN Performance under no load: 
ACCESS type               sequential        random 
                        IOPS     MBPS     IOPS    MBPS 
 8 kb block  read    17003,20  132,83   145,51    1,13
 16 kb block  read   13150,31  205,47   142,36    2,22
 64 kb block  read    6367,48  397,96   131,03    8,18
 128 kb block  read   3250,58  406,32   110,4    13,80
 256 kb block  read   2063,60  515,90   96,63    24,15
 512 kb block  read   1205,55  602,77   88,45    44,22
DATA LUN Performance under load: (the load is 8 kb block sequential write to LOG LUN) 
ACCESS type               sequential        random 
                        IOPS     MBPS     IOPS     MBPS 
 8 kb block  read   17280,98   135,00    144,11   1,12
 16 kb block  read  14502,53   226,60    141,00   2,20
 64 kb block  read   6922,3    432,64    130,90   8,18
 128 kb block  read  3309,61   413,70    108,53  13,56
 256 kb block  read  2344,68   586,17     95,68  23,92
 512 kb block  read  1205,9    602,95     87,55  43,77

Now draw the conclusions on you own :)

Regards,
Slawek
Slawek Guzek
Dynamics NAV, MS SQL Server, Wherescape RED;
PRINCE2 Practitioner - License GR657010572SG
GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03

Comments

  • Options
    davmac1davmac1 Member Posts: 1,283
    Maybe your vendor does not understand how log files are written to disk and how it relates to head movement.
    David Machanick
    http://mibuso.com/blogs/davidmachanick/
  • Options
    krikikriki Member, Moderator Posts: 9,089
    I read once that BEFORE buying a SAN, you should ask the SAN-vendor for a whitepage on how to prepare the SAN for SQL. If he refuses or cannot give it, search for another vendor.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

  • Options
    David_SingletonDavid_Singleton Member Posts: 5,479
    kriki wrote:
    I read once that BEFORE buying a SAN, you should ask the SAN-vendor for a whitepage on how to prepare the SAN for SQL. If he refuses or cannot give it, search for another vendor.

    I will second that. :D
    David Singleton
  • Options
    David_SingletonDavid_Singleton Member Posts: 5,479
    About a year or so ago, I had a client with SQL performance problems, and searching SQL books on MSDN, I found a great article where they did testing with a SAN to show why the LOG file needs to be on a separate dedicated spindle. They showed that a single dedicated RAID1 allocated to actual drives , compared to having the same log file on a LUN that was visualized over 26 spindles. The logic seemed to be that if you had 26 spindles (so in RAID 10, at least 52 drives) then its starts to balance out letting the virtualization stripe the LOG file. but less than that then the best is to have the Log file dedicated.

    The simple rule is SANs are great... but ONLY if you know how to correctly configure them.
    David Singleton
  • Options
    DenSterDenSter Member Posts: 8,304
    Just to drive the point home:
    David Singleton wrote:
    The simple rule is SANs are great... but ONLY if you know how to correctly configure them.
    Daniel Rimmelzwaan
    RIS Plus, LLC
    MVP - Business Apps
  • Options
    krikikriki Member, Moderator Posts: 9,089
    David Singleton wrote:
    About a year or so ago, I had a client with SQL performance problems, and searching SQL books on MSDN, I found a great article where they did testing with a SAN to show why the LOG file needs to be on a separate dedicated spindle. They showed that a single dedicated RAID1 allocated to actual drives , compared to having the same log file on a LUN that was visualized over 26 spindles. The logic seemed to be that if you had 26 spindles (so in RAID 10, at least 52 drives) then its starts to balance out letting the virtualization stripe the LOG file. but less than that then the best is to have the Log file dedicated.
    If you could find back that article and post it in this thread or on your blog, that would be great!
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

  • Options
    nunomaianunomaia Member Posts: 1,153
    kriki wrote:
    David Singleton wrote:
    About a year or so ago, I had a client with SQL performance problems, and searching SQL books on MSDN, I found a great article where they did testing with a SAN to show why the LOG file needs to be on a separate dedicated spindle. They showed that a single dedicated RAID1 allocated to actual drives , compared to having the same log file on a LUN that was visualized over 26 spindles. The logic seemed to be that if you had 26 spindles (so in RAID 10, at least 52 drives) then its starts to balance out letting the virtualization stripe the LOG file. but less than that then the best is to have the Log file dedicated.
    If you could find back that article and post it in this thread or on your blog, that would be great!

    Yes that would be nice. :wink:
    Nuno Maia

    Freelance Dynamics AX
    Blog : http://axnmaia.wordpress.com/
Sign In or Register to comment.