High iowait while the wMB/s is low

Question

I was indexing a MySQL table. It makes a high load on that computer.

It seems due to a high iowait. But it also shows that the wMB/s is only 2.87.

Isn't even a common SATA HDD able to handle more than 2.87MB/s? Why is process so slow then?

iostat -x reports:

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           1.74    0.00    3.48   47.51    0.00   47.26

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await  svctm  %util
sda               0.00   300.00    0.00  383.00     0.00     2.87    15.35   142.00  374.64   2.61  99.90
sdb               0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
scd0              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdc               0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
dm-0              0.00     0.00    0.00 2507.00     0.00     9.79     8.00   263.88  110.06   0.40  99.90
dm-1              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
dm-2              0.00     0.00    0.00    2.00     0.00     0.01     8.00     0.41  196.00 202.50  40.50
dm-3              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00

Solution 1

You're doing small random writes, which is pretty much the slowest thing you can do on a spinning disk, so I would say your throughput meets (my) expectations.

Your avgrq-sz size is 15.35, which means your average request is 15.35 x the sector size of your SATA disk (most commonly 512 bytes, but possibly 4096 bytes on a very new SATA disk), so you're writing 15.35 x 512 bytes = 7,859.2 bytes (on average) per request, times the 383 writes/second iostat is reporting gives you 3,010,073.6 bytes (we're multiplying by an average, so that's where the .6 bytes comes from). And 3,010,073.6 bytes/second is 2.87MB/s.

How many writes you can do per second is going to depend on how much the disk needs to move the heads, but roughly speaking you are approaching the maximum number of writes your device can make in a second.

Higher write speeds on spinning disks happen when you combine a small number of writes per second with a large avgrq-sz.

If this is a critical performance issue for you, I'd suggest investigating the various SSD options that generally will give much better performance on a workload like this.

Solution 2

You need to run iostat several times while monitoring this to build up a true picture of what's going on. That, or use a tool like Cacti to keep such statistics for you over time so you can look at the historical graph.

What's most likely happening is that the disk is also doing a lot of reads, due to the DB table scan, and the iostat run you posted just happened to be done during a time when the DBMS was writing rather than reading. Writing interleaved with reading is really slow on a hard disk because it involves seeking, which is the slowest thing a hard drive does. If you listen to the hard drive, you can probably hear it rattling madly as it rapidly alternates between writing and reading to different portions of the hard disk.

To make this process run at the double-digit MByte/s rate you're expecting, you'd have to split the process in two, building up the index in RAM while doing the table scan, then writing the complete index out. MySQL is highly-optimized software, so if it could do this, I expect it would, and since it isn't, it can't. That is to say, it probably doesn't have enough RAM to do that. That either means you don't have enough physical RAM in the machine for MySQL's portion to contain the complete index, or that you haven't given it a high enough portion of the system's RAM in the MySQL configuration file. Tuning MySQL is a topic for a different forum, however.