Post reply

SQLIO

  • September 26, 2013 at 8:10 pm

    #299294

    Hi, I'm trying to bench mark the disk IO for my PROD SQL server against SAN.

    We have 64 GB Array cache. I have created 100 GB test file using sqlio and ran a sample test as using below code:

    The param.txt has the code:

    e:\testfile.dat 2 0x0 102400

    C:\Program Files (x86)\SQLIO>sqlio -kW -s10 -fsequential -t8 -o8 -b8 -LS -Fpara

    .txt timeout /T 10

    sqlio v1.5.SG

    using system counter for latency timings, 14318180 counts per second

    parameter file used: param.txt

    file e:\testfile.dat with 2 threads (0-1) using mask 0x0 (0)

    2 threads writing for 10 secs to file e:\testfile.dat

    using 8KB sequential IOs

    enabling multiple I/Os per thread with 8 outstanding

    size of file e:\testfile.dat needs to be: 107374182400 bytes

    current file size: 104857600 bytes

    need to expand by: 107269324800 bytes

    expanding e:\testfile.dat ... done.

    using specified size: 102400 MB for file: e:\testfile.dat

    initialization done

    CUMULATIVE DATA:

    throughput metrics:

    IOs/sec: 3932.40

    MBs/sec: 30.72

    latency metrics:

    Min_Latency(ms): 0

    Avg_Latency(ms): 3

    Max_Latency(ms): 14

    histogram:

    ms: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+

    %: 0 0 1 58 34 5 2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

    C:\Program Files (x86)\SQLIO>

    Question:

    Are the above IOS/sec and MBs/sec values good?

    I will run different tests using the script provided by Brent at http://www.brentozar.com/archive/2008/09/finding-your-san-bottlenecks-with-sqlio/

    sqlio -kW -t8 -s120 -o8 -frandom -b8 -BH -LS E:\TestFile.dat

    sqlio -kR -t8 -s120 -o8 -frandom -b8 -BH -LS E:\TestFile.dat

    sqlio -kW -t8 -s120 -o8 -fsequential -b64 -BH -LS E:\TestFile.dat

    sqlio -kR -t8 -s120 -o8 -fsequential -b64 -BH -LS E:\TestFile.dat

    but before that just wanted to know if we get any idea by seeing the above disk IO values.

    Thanks,

    Gary

  • September 27, 2013 at 12:38 pm

    #1654166

    Hi,

    I ran a sample SQLIO stress test using the below parameters in a batch file called Test.bat files.

    I'm just using 100 MB file to start.for the next run, I'm going to use 100 GB file as our SAN array cache is 64 GB

    sqlio -kW -t8 -s120 -o8 -frandom -b8 -BH -LS E:\TestFile.dat

    sqlio -kR -t8 -s120 -o8 -frandom -b8 -BH -LS E:\TestFile.dat

    sqlio -kW -t8 -s120 -o8 -fsequential -b64 -BH -LS E:\TestFile.dat

    sqlio -kR -t8 -s120 -o8 -fsequential -b64 -BH -LS E:\TestFile.dat

    Here are the results: Based on below results, do you see any issue on IO bottle neck?

    C:\Program Files (x86)\SQLIO>sqlio -kW -t8 -s120 -o8 -frandom -b8 -BH -LS E:\TestFile.dat

    sqlio v1.5.SG

    using system counter for latency timings, 14318180 counts per second

    8 threads writing for 120 secs to file E:\TestFile.dat

    using 8KB random IOs

    enabling multiple I/Os per thread with 8 outstanding

    buffering set to use hardware disk cache (but not file cache)

    using current size: 100 MB for file: E:\TestFile.dat

    initialization done

    CUMULATIVE DATA:

    throughput metrics:

    IOs/sec: 4669.30

    MBs/sec: 36.47

    latency metrics:

    Min_Latency(ms): 1

    Avg_Latency(ms): 13

    Max_Latency(ms): 145

    histogram:

    ms: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+

    %: 0 0 0 0 0 0 0 0 0 0 0 1 31 38 18 6 3 1 1 0 0 0 0 0 0

    C:\Program Files (x86)\SQLIO>sqlio -kR -t8 -s120 -o8 -frandom -b8 -BH -LS E:\TestFile.dat

    sqlio v1.5.SG

    using system counter for latency timings, 14318180 counts per second

    8 threads reading for 120 secs from file E:\TestFile.dat

    using 8KB random IOs

    enabling multiple I/Os per thread with 8 outstanding

    buffering set to use hardware disk cache (but not file cache)

    using current size: 100 MB for file: E:\TestFile.dat

    initialization done

    CUMULATIVE DATA:

    throughput metrics:

    IOs/sec: 6535.77

    MBs/sec: 51.06

    latency metrics:

    Min_Latency(ms): 0

    Avg_Latency(ms): 9

    Max_Latency(ms): 108

    histogram:

    ms: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+

    %: 0 0 0 0 0 0 0 0 14 60 17 4 2 1 0 0 0 0 0 0 0 0 0 0 0

    C:\Program Files (x86)\SQLIO>sqlio -kW -t8 -s120 -o8 -fsequential -b64 -BH -LS E:\TestFile.dat

    sqlio v1.5.SG

    using system counter for latency timings, 14318180 counts per second

    8 threads writing for 120 secs to file E:\TestFile.dat

    using 64KB sequential IOs

    enabling multiple I/Os per thread with 8 outstanding

    buffering set to use hardware disk cache (but not file cache)

    using current size: 100 MB for file: E:\TestFile.dat

    initialization done

    CUMULATIVE DATA:

    throughput metrics:

    IOs/sec: 2032.83

    MBs/sec: 127.05

    latency metrics:

    Min_Latency(ms): 2

    Avg_Latency(ms): 30

    Max_Latency(ms): 331

    histogram:

    ms: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+

    %: 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 100

    C:\Program Files (x86)\SQLIO>sqlio -kR -t8 -s120 -o8 -fsequential -b64 -BH -LS E:\TestFile.dat

    sqlio v1.5.SG

    using system counter for latency timings, 14318180 counts per second

    8 threads reading for 120 secs from file E:\TestFile.dat

    using 64KB sequential IOs

    enabling multiple I/Os per thread with 8 outstanding

    buffering set to use hardware disk cache (but not file cache)

    using current size: 100 MB for file: E:\TestFile.dat

    initialization done

    CUMULATIVE DATA:

    throughput metrics:

    IOs/sec: 2477.82

    MBs/sec: 154.86

    latency metrics:

    Min_Latency(ms): 1

    Avg_Latency(ms): 25

    Max_Latency(ms): 278

    histogram:

    ms: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+

    %: 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 6 15 77

Viewing 2 posts - 1 through 1 (of 1 total)

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