SQL Server Performance

Weird raid 0+1 behavior

Discussion in 'Performance Tuning for Hardware Configurations' started by Luke888, Feb 4, 2006.

  1. Luke888 New Member

    Got some new server hardware, and trying to determine the best suitable raid configuraion for our data disk.

    Im meassuring with sqlio, and see some weird behavior between raid 5 and rad 0+1. We have 4x72 GB sas drives on a HP P600 raid controller. It's a proliant DL385, dual opeteron 275 so basicly there are 4 CPU's. OS is Win 2003 STD R2 x64.

    Read performance is more or less equal in raid 5 and 0+1 as expected, as data reads can be split up in all 4 disks. But write performance is weird.

    Both test's have set controller write cache for 75/25 read/write.

    The sqlio parameters are the same as here, though only with30 threads
    http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=10495

    Were doing 8 write test, 4 random and 4 sequential.

    RAID 5 test
    1. 8KB random IOs/sec: 505.28 MBs/sec: 3.94
    2. 64KB random IOs/sec: 214.81 MBs/sec: 13.42
    3. 128KB random IOs/sec: 189.51 MBs/sec: 23.68
    4. 256KB random IOs/sec: 116.73 MBs/sec: 29.18
    5. 8KB sequential IOs/sec: 14032.25 MBs/sec: 109.62
    6. 64KB sequential IOs/sec: 2336.03 MBs/sec: 146.00
    7. 128KB sequential IOs/sec: 1195.26 MBs/sec: 149.40
    8. 256KB sequential IOs/sec: 173.94 MBs/sec: 43.48

    RAID 0+1 test
    1. 8KB random IOs/sec: 774.54 MBs/sec: 6.05
    2. 64KB random IOs/sec: 390.66 MBs/sec: 24.41
    3. 128KB random IOs/sec: 280.82 MBs/sec: 35.10
    4. 256KB random IOs/sec: 181.02 MBs/sec: 45.25
    5. 8KB sequential IOs/sec: 6220.69 MBs/sec: 48.59
    6. 64KB sequential IOs/sec: 930.09 MBs/sec: 58.13
    7. 128KB sequential IOs/sec: 633.25 MBs/sec: 79.15
    8. 256KB sequential IOs/sec: 463.25 MBs/sec: 115.81

    As expected test 1-4 are almost doubled in raid 0+1, as data can be halfed before writing.

    But test 5-8 are really weird, I can't see why they should'nt double as well, and suspect it might be a driver issue. (firmware and drivers are up to date)

    Can any please enlighten me on what's going on?
  2. Twan New Member


    I don't have the skills to interpret the results you have, but you do mean RAID 1+0 and not RIAD 0+1 I presume...? RAID 0+1 offers too little redundancy compared with RAID 1+0

    Cheers
    Twan
  3. peterlemonjello New Member

    Random IO looks normal but WOW on the sequential differences. I would expect the RAID 5 sequential to be flip flopped with the RAID 0+1 sequential.
  4. joechang New Member

    i would suggest you test read & write separately, as it is easier to interpret the results
  5. Luke888 New Member

    "Twan"
    I don't have the skills to interpret the results you have, but you do mean RAID 1+0 and not RIAD 0+1 I presume...? RAID 0+1 offers too little redundancy compared with RAID 1+0
    >> I mean raid 0+1, not sure what 1+0 is and it not an option for this controller.

    "joechang"
    i would suggest you test read & write separately, as it is easier to interpret the results
    >> The write tests are seperate, all of the above 8 tests are write.

    "peterlemonjello"
    Random IO looks normal but WOW on the sequential differences. I would expect the RAID 5 sequential to be flip flopped with the RAID 0+1 sequential.
    >> not sure what you mean, but if you are refering stripe size, i tried with different size with the same result. Both tests are done on empty drives, formatted the same way.

    Test test batch is as follows:
    echo test 1
    "C:program Files (x86)SQLIOsqlio" -kW -s60 -frandom -o8 -b8 -LP -Fparam.txt timeout /T 30 > result.txt
    echo test 2
    "C:program Files (x86)SQLIOsqlio" -kW -s60 -frandom -o8 -b64 -LP -Fparam.txt timeout /T 30 >> result.txt
    echo test 3
    "C:program Files (x86)SQLIOsqlio" -kW -s60 -frandom -o8 -b128 -LP -Fparam.txt timeout /T 30 >> result.txt
    echo test 4
    "C:program Files (x86)SQLIOsqlio" -kW -s60 -frandom -o8 -b256 -LP -Fparam.txt timeout /T 30 >> result.txt
    echo test 5
    "C:program Files (x86)SQLIOsqlio" -kW -s60 -fsequential -o8 -b8 -LP -Fparam.txt timeout /T 30 >> result.txt
    echo test 6
    "C:program Files (x86)SQLIOsqlio" -kW -s60 -fsequential -o8 -b64 -LP -Fparam.txt timeout /T 30 >> result.txt
    echo test 7
    "C:program Files (x86)SQLIOsqlio" -kW -s60 -fsequential -o8 -b128 -LP -Fparam.txt timeout /T 30 >> result.txt
    echo test 8
    "C:program Files (x86)SQLIOsqlio" -kW -s60 -fsequential -o8 -b256 -LP -Fparam.txt timeout /T 30 >> result.txt
  6. peterlemonjello New Member

    I would have expected your RAID 5 sequential results to be the results for the RAID 1+0, and the RAID 1+0 results to be the results for the RAID 5.

    In all of my SQLIO testing (which is limited by the way) the RAID 1+0 configuration always outperforms RAID 5 configs as long as the same number of drives are used for both. Doesn't matter if it's sequential or random or read or write RAID 1+0 always outperformed RAID 5.
  7. joechang New Member

    my bad,for some reason i thought you were posting 75/25 read write results

    RAID 5 should have better sequential write than RAID 1+0,
    but it should get better with big blocks, not fall over preciptously.
    your RAID 1+0 sequential writes are probably ok, even though lower than i would expect for small blocks

    try changing the cache settings to 10% read, 90% write, as this is preferred for database ops, or even 100% write
  8. Luke888 New Member

    well, as always Joe is right!!! yawn <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />Did the below tests with blocks ranging from 128, 256, 512, 1024 KB, instead of the above that was 8, 64, 128, 256KB<br /><br />Raid 5<br /> 128 KB random IOs/sec: 218.40 MBs/sec: 27.30<br /> 256 KB random IOs/sec: 110.73 MBs/sec: 27.68<br /> 512 KB random IOs/sec: 91.00 MBs/sec: 45.50<br />1024 KB random IOs/sec: 45.90 MBs/sec: 45.90<br /> 128 KB sequential IOs/sec: 1243.96 MBs/sec: 155.49<br /> 256 KB sequential IOs/sec: 169.19 MBs/sec: 42.29<br /> 512 KB sequential IOs/sec: 120.68 MBs/sec: 60.34<br />1024 KB sequential IOs/sec: 79.99 MBs/sec: 79.99<br /><br />Raid 0+1<br /> 128 KB random IOs/sec: 294.36 MBs/sec: 36.79<br /> 256 KB random IOs/sec: 171.01 MBs/sec: 42.75<br /> 512 KB random IOs/sec: 137.68 MBs/sec: 68.84<br />1024 KB random IOs/sec: 55.08 MBs/sec: 55.08<br /> 128 KB sequential IOs/sec: 585.32 MBs/sec: 73.16<br /> 256 KB sequential IOs/sec: 433.55 MBs/sec: 108.38<br /> 512 KB sequential IOs/sec: 232.02 MBs/sec: 116.01<br />1024 KB sequential IOs/sec: 81.82 MBs/sec: 81.82<br /><br />When the block size is from 256 and over, raid 0+1 outperformes raid 5.<br /><br />Now my question is:<br />I head some where that SQL Server always writes in 64 KB block? is this right, and more importantly will this influense my choise of raid?<br />
  9. peterlemonjello New Member

    All things being equal, I always use RAID 1+0 for data LUNS as long as space isn't an issue. If I need more space then I'll consider RAID 5.

    Check out the SQLIOStress utility. This is different than SQLIO. It simulates read and write patterns of a heavily loaded server running SQL Server and it uses the Write Ahead Logging protocol used in SQL Server. This should provide the best info to make your RAID decision.

    http://support.microsoft.com/default.aspx?scid=kb;en-us;231619
  10. joechang New Member

    what was i right about? was i drinking when i said whatever?

    ok, it was a good idea to extend the range of block sizes,
    i think what was happening is that the small block raid5 sequential was being cached, so the fall off at 256K was really just the true disk bound result, which is not unnreasonable

    now even though many people say use raid10 for data, keep in mind this assessment was made when memory was more expensive
    now, most people have so much data in memory, that i think we need to reassess whether we can use raid 5 for data in high buffer cache hit environments.
    i think the most stressful disk event on many S2K installations is the check point,
    have a script update random row, then wait for the checkpoint, is the disks in raid5 can handle, it may be ok,
    i would still not skimp on number disks, so do not use the rational that raid 5 is more efficient in terms of capacity, never skimp on the number of spindles

Share This Page