Random thoughts on a MySQL Disk Bound Benchmark

So since last talking about a new benchmark I found a need for one.  Specifically the need to truly test IO bound workloads in mysql to flex fast storage subsystems.  Tackling a new benchmark is not as easy as I thought.  I am already on version 2 of my code and its still really a mess.  But it appears solid enough to try and give it a few test runs on some really fast disk subsystems.  So Armed with several really bad queries I put together a read-only workload that just hammers the disk and little else…  I am working a little under the gun as I only have access to the TMS Ramsan for 1 more night, and only have access to a Violin memory test box for a short while longer as well.  The problem I am running into with these systems is DBT2 and sysbench bottleneck the CPU before they really flex the disk.  I want to flex the disk damn it!

So how’s it going?  reasonably well. I am not happy with everything and I see lots of places where I did things the hard way in my code, but I can now generate a configurable size database, and hit it with several queries do large full & index scans.  So far with 4 threads on my server at home ( 10k Sata disks ) I end up generating this type of load:

%user   %nice %system %iowait  %steal   %idle
0.68    0.00    0.27   23.64    0.00   75.41

Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
sda               0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdb               0.00     7.80    0.00    0.90     0.00    69.60    77.33     0.00    3.56   3.11   0.28
sdc               0.70     0.00  146.40    0.00  3925.60     0.00    26.81     2.05   14.02   6.27  91.72
sdd               0.50     0.00  147.90    0.00  4004.80     0.00    27.08     2.16   14.55   6.14  90.88
sde               0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
md0               0.00     0.00  295.50    0.00  7929.60     0.00    26.83     0.00    0.00   0.00   0.00

%user   %nice %system %iowait  %steal   %idle
0.73    0.00    0.44   23.41    0.00   75.41

Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
sda               0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdb               0.00     0.00    0.00    0.30     0.00     2.40     8.00     0.01   17.33   6.67   0.20
sdc               1.00     0.00  146.20    0.00  3896.00     0.00    26.65     2.22   15.15   6.26  91.48
sdd               0.70     0.00  148.30    0.00  3959.20     0.00    26.70     1.98   13.40   6.03  89.40
sde               0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
md0               0.00     0.00  296.20    0.00  7856.00     0.00    26.52     0.00    0.00   0.00   0.00

%user   %nice %system %iowait  %steal   %idle
0.68    0.00    0.29   23.63    0.00   75.40

Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
sda               0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdb               0.00     0.60    0.00    1.40     0.00    16.00    11.43     0.00    2.86   0.57   0.08
sdc               1.50     0.00  147.30    0.00  3954.40     0.00    26.85     2.41   16.35   6.47  95.32
sdd               0.80     0.00  147.40    0.00  3963.20     0.00    26.89     1.82   12.29   6.01  88.56
sde               0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
md0               0.00     0.00  297.00    0.00  7916.80     0.00    26.66     0.00    0.00   0.00   0.00

So I am definitely on my way to hitting my goal of making the 10K disk cry uncle. Are these tests realistic? Umm for most people no. These queries do have a lot in common with the bad queries I go out and fix every day… so yes these are unoptimized… but that is part of what I going for.  I can acheive load by slamming 1K threads querying off of the PK, but I am going to end up sucking up a lot of CPU…  so instead why not do some nasty full table scans mixed with some just as nasty index scans.

I really do hope this works….  stay tuned.

This entry was posted in hardware, mysql, performance. Bookmark the permalink.

4 Responses to Random thoughts on a MySQL Disk Bound Benchmark

  1. Hander says:

    Hi! What iostat parameters did you used in this benchmarks?

    Thnx!

  2. matt says:

    iostat -x 10

  3. Matt,

    In my experience the CPU cost of buffer pool miss (testing checksum dealing with LRU etc) is way to high with Innodb. Plus high IO rate exposes yet another contentions not seen in normal systems.

    To stress IO with Innodb you may wish to disable checksums and use rather small buffer pool… still you may be out of luck loading storage to full capacity.

    If you just evaluate storage consider playing with MyISAM too which often can load IO system better (especially if you disable key cache)

  4. matt says:

    Peter,

    Thanks for the info in the checksum. I think I still have some room doing a mix of higher IO operations on flash devices… I am planning to push it till I break it, and then try and fix it:)