Bug #68659 InnoDB Linux native aio should submit more i/o requests at once
Submitted: 13 Mar 2013 7:47 Modified: 13 Mar 2013 9:01
Reporter: Yoshinori Matsunobu (OCA) Email Updates:
Status: Analyzing Impact on me:
Category:MySQL Server: InnoDB storage engine Severity:S5 (Performance)
Version:5.6.10 OS:Any
Assigned to: CPU Architecture:Any

[13 Mar 2013 7:47] Yoshinori Matsunobu
[mysql-5.6.10/storage/innobase]$ grep -r io_submit ./
./os/os0file.cc:        int     err = io_submit(io_ctx, 1, &p_iocb);
./os/os0file.cc:        ret = io_submit(array->aio_ctx[io_ctx_index], 1, &iocb);

Second argument of io_submit() is # of i/o requests. 1 means InnoDB sends only one 16KB (or zip size) aio request at one time. On Linux kernel layer, i/o unit size is close to 16KB, too. This is too small for full table scan.

Here is an example iostat when I ran mysqldump (one client), read ahead enabled.

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await  svctm  %util
sdc            5097.30     8.70 1810.40    1.60    26.98     0.04    30.54     0.79    0.43   0.41  74.99

26.98MB/1810=15.26KB. 26.98MB/s is much better than MySQL 5.1 (native aio is not supported) if there is only one client. But 1810 r/s is already too high (for HDD), so upper bound of read i/o volume is limited.

Here is another iostat result - 64 concurrent mysqldump.

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await  svctm  %util
sdc            8336.90   836.90 5055.20   36.60    52.27     3.41    22.40   139.11   27.26   0.20 100.00

52.27MB/s is not great in general, and was close to the maximum i/o volume in MySQL 5.1 when I tested.

I think 5.6 InnoDB should submit multiple i/o requests at one time as long as possible, especially when doing readahead.

The following are micro benchmarking results when submitting 1 or 64 async i/o requests of 16K or 1M block. Storage layer is HDD RAID1+0. r/s and rMB/s are from iostat -xm.

16kb submit 1 -> 10359 r/s, 161.86 rMB/s
16kb submit 64 -> 5535 r/s, 1106.81 rMB/s
1mb  submit 1 -> 4845 r/s, 1211.27 rMB/s
1mb submit 64 -> 4132 r/s, 1032.91 rMB/s

Apparently submitting multiple 16KB i/o requests at once, or submitting one large i/o request (one extent for example) is more efficient than submitting one 16KB i/o request.

How to repeat:
Set innodb_flush_method=O_DIRECT, run SELECT * FROM large_enough_innodb_table and see how read iops and read i/o bytes from iostat.
[13 Mar 2013 9:10] Yoshinori Matsunobu
Simple aio program that I used (usage: g++ aio.cc -laio -o aio; ./aio /dev/sdx 1 16

Attachment: aio.cc (text/plain), 1.24 KiB.