Looking for comparisons of disk i/o for a dedicated MySQL server

Question

We hired a consultant to help us increase the capacity of our MySQL cluster, and the first thing (almost only thing) he did was to measure the disk i/o speed of our servers.

I am interested in a comparison of disk i/o on similar systems to what we have:

• Our MySQL servers are virtual servers running on 32 bit VMWare ESX 3.5 with a SCSI SAN (Raid 5), and the Virtual servers themselves are running Debian Etch, and MySQL version 5.0.32

Running the following commands on the MySQL box give these results for me (which the consultant described as "Terribly slow":

time dd if=/dev/zero of=OUT.tmp bs=1M count=1000
1000+0 records in
1000+0 records out
1048576000 bytes (1.0 GB) copied, 71.3347 seconds, 14.7 MB/s
real    1m13.596s
user    0m0.052s
sys 0m56.932s

time dd if=OUT.tmp of=/dev/null bs=1M count=1000
1000+0 records in
1000+0 records out
1048576000 bytes (1.0 GB) copied, 21.8202 seconds, 48.1 MB/s
real    0m21.902s
user    0m0.012s  
sys 0m7.948s

Are these results indeed "Terribly slow"?

I would be interested in comparing the results other people get from these 2 commands on their dedicated MySQL boxes - particularly if it is a 32-bit virtual machine.

Answer 1

Something to be aware of is that your dd command will not be bypassing the OS's filesystem cache. This means you'll get varying results depending on what else is going on, and you'll notice significant performance variation as you increase the output size (and thus exhaust your fs cache)

Add the "oflag=direct" to bypass the filesystem cache on the output file, eg

time dd if=/dev/zero of=OUT.tmp bs=1M count=1000 oflag=direct

You can bypass filesystem cache for read by using iflag=direct

Also, your performance will vary greatly with blocksize. While 1M is a pretty good tradeoff for testing sequential writes, unless your application is writing out 1M blocks it's not going to be representative of your actual performance.

As a general point, those throughput figures are pretty abysmal - a single sata drive (such as the Seagate ES.2 drives) can peak at 105MB/sec sequential write at the start of the drive, and will sustain ~60MB/sec over the whole drive.

Finally, general database "best practices" say to avoid RAID5/6 as an underlying system for a database, due to the relatively high overhead caused by parity writes (not the actual parity calculation itself, which is fairly cheap in hardware, but the effect of having to do extra reads and writes when writing out new parity).

Answer 2

Here's the results from my mysql server. It's a 64bit, not virtual machine, so not sure how much use it really is, but there is a very considerable difference.

time dd if=/dev/zero of=OUT.tmp bs=1M count=1000
1000+0 records in
1000+0 records out
1048576000 bytes (1.0 GB) copied, 5.72139 s, 183 MB/s
0.00s user 1.55s system 27% cpu 5.725 total

time dd if=OUT.tmp of=/dev/null bs=1M count=1000
1000+0 records in
1000+0 records out
1048576000 bytes (1.0 GB) copied, 0.432328 s, 2.4 GB/s
0.00s user 0.45s system 103% cpu 0.436 total

Answer 3

in most cases you should also be comparing random io [ eg with bonnie++ ] not only linear read/write. or maybe it's one big data sink that takes logs and stores in un-indexed huge table?

results for dd 'benchmark'

szcapp1:/mnt/big/tmp# time dd if=/dev/zero of=OUT.tmp bs=1M count=1000
time dd if=OUT.tmp of=/dev/null bs=1M count=1000
1000+0 records in
1000+0 records out
1048576000 bytes (1.0 GB) copied, 4.26186 s, 246 MB/s

real    0m4.563s
user    0m0.001s
sys     0m2.255s
szcapp1:/mnt/big/tmp# time dd if=OUT.tmp of=/dev/null bs=1M count=1000
1000+0 records in
1000+0 records out
1048576000 bytes (1.0 GB) copied, 0.457162 s, 2.3 GB/s

real    0m0.459s
user    0m0.000s
sys     0m0.459s
szcapp1:/mnt/big/tmp#

64bit linux on dell poweredge 2950, perc6 raid 10 on 5x desktop 500GB sata disks. 16GB ram, 2x quad core 2.66GHz. but hey! this does not make sense - this data fits in 1/4 in cache memory of raid controler, and rest - in the system memory.

your results are slow indeed. results from vm running on linux above [ 32bit linux guest under vmware server 2.0 ]:

vfeed0:/tmp# time dd if=/dev/zero of=OUT.tmp bs=1M count=1000
1000+0 records in
1000+0 records out
1048576000 bytes (1.0 GB) copied, 15.996 s, 65.6 MB/s

real    0m16.043s
user    0m0.016s
sys     0m13.117s
vfeed0:/tmp# time dd if=OUT.tmp of=/dev/null bs=1M count=1000
1000+0 records in
1000+0 records out
1048576000 bytes (1.0 GB) copied, 0.49413 s, 2.1 GB/s

real    0m0.505s
user    0m0.000s
sys     0m0.500s
vfeed0:/tmp#

keep in mind that read performance is fake - it's read from the cache - if not from the cache of guest, then surly from cache of vmware host.

Answer 4

In addition to what Daniel Lawson has noted about the cached GB/s results..

If your dd doesn't happen to support iflag/oflag then you can manually flush the disk cache between runs with the command:

sync; echo 3 > /proc/sys/vm/drop_caches

Or simply using a dataset that is twice as large as your RAM will ensure the data never has a chance to be cached between runs.

---

As for your consultant's dd test - it's a good starting point, but not a conclusive test. It is measuring sequential I/O. That is the reading and writing blocks that lie immediately next to one another on the physical disk. Unfortunately databases very rarely deal with sequential I/O. Records get written at different times and are usually not retrieved, over time, in the order which they were written.

With this in mind, the two utmost important factors that you need to measure are random I/O throughput and latency. These will have the greatest impact as to how MySQL behaves. To measure these I'd suggest using the utility bonnie++. It has preset tests to benchmark all of the above and is far more configurable if you want to test specific aspects.

You can also use MySQL super-smack to measure the repeated performance of individual SQL queries.

Answer 5

Somewhat away from your original question; but the SAN vendor response to "RAID 5 is slow", is to convert to RAID 1, or RAID 10. Also consider VMFS alignment (PDF) may be severely impacting performance.

Answer 6

When my crappy old Dell Latitude D520 (80Go;5400RPM) challenges your MySQL server you know there is a problem somewhere ^^ :

$ time dd if=/dev/zero of=OUT.tmp bs=1M count=1000
1000+0 records in
1000+0 records out
1048576000 bytes (1.0 GB) copied, 32.5178 s, 32.2 MB/s
real    0m32.662s
user    0m0.008s
sys 0m2.716s

$ time dd if=OUT.tmp of=/dev/null bs=1M count=1000
1000+0 records in
1000+0 records out
1048576000 bytes (1.0 GB) copied, 29.8311 s, 35.2 MB/s
real    0m29.892s
user    0m0.004s
sys 0m2.056s

You should first have a look on the strain those two tests put on the I/O load of the ESX nodes.

Then do a bonnie++ test coupled with iostat and mpstat running in parallel shells (ahoy screen !) on thoses nodes and on the SAN to find where the bottleneck stands. You'll surely find high amounts of iowaits somewhere, that will be the part to concentrate on.