Why would MySQL do serial synchronous I/O?

When looking at a particularly annoying query over MyISAM tables which takes a long time to execute on a number of occasions, I have noted that MySQL seems to expose a rather strange I/O pattern: when executing a single query and having to do a significant amount of I/O (e.g for a table scan or when caches are empty as a result of echo 3 > /proc/sys/vm/drop_caches so the indexes need to be loaded off disk first), the queue size for the underlying block device is near the value 1, with abysmal performance of just 4-5 MB/s:

root@mysql-test:~# iostat -xdm 5 /dev/sda
Linux 3.2.0-40-generic (mysql-test)  04/30/2014      _x86_64_        (4 CPU)

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sda               0.14    24.82   18.26   88.79     0.75     4.61   102.56     2.83   26.39   19.29   27.85   2.46  26.31

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sda               0.00    69.29  151.52   72.73     5.31     0.59    53.95     1.21    5.39    7.84    0.29   4.39  98.51

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sda               0.00   153.06  144.29  174.69     4.96     1.36    40.54     1.39    4.36    8.91    0.60   3.15 100.49

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sda               0.00   105.75  150.92  109.03     4.53     0.85    42.41     1.29    4.96    8.15    0.54   3.90 101.36

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sda               0.00    48.89  156.36   51.72     5.28     0.76    59.38     1.28    6.16    8.02    0.55   4.77  99.23

While the 150 IOPS simply are what a single disk in the given configuration is capable of delivering in terms of ran

First let me clarify by confirming that MyISAM does not do asynchronous I/O, but that InnoDB does and will by default from MySQL 5.5. Prior to 5.5 it used "simulated AIO" by using worker threads.

I think it is also important to distinguish between three situations:

• Multiple queries executing at once

• A single query executing in parallel

• Some sort of logical read ahead for table scans / clear cases where the next pages are well known.

For (1) I/O will be able to execute in parallel for this. There are some limits with MyISAM: table locking and a global lock protecting the key_buffer (index cache). InnoDB in MySQL 5.5+ really shines here.

For (2) this is currently not supported. A good use case would be with partitioning, where you could search each partitioned table in parallel.

For (3) InnoDB has linear read-ahead to read a full extent (group of 64 pages) if >56 pages are read (this is configurable), but there is room for further enhancement. Facebook has written about implementing logical-readhead in their branch (with a 10x perf gain on tablescans).

StackExchange Database Administrators Q#64206, answer score: 8