This patch adds new configuration settings to control InnoDB's I/O behavior, which has been hardcoded in standard InnoDB for many years. This is a port of Google's InnodbIoTuning and InnodbAsyncIo patches.

The patch provides the following variables:

The number of background I/O threads for read prefetch requests.

The number of background I/O threads for writing dirty pages from the buffer pool.

The maximum number of I/O requests merged to issue large I/O from background I/O threads.

The number of disk I/O operations InnoDB assumes the server can perform per second. InnoDB assumes by default that the server can perform 100 I/O per second, which is not always true. The patch adjusts InnoDB's behavior to consume more I/O resources when it's told they are available.

This variable instructs InnoDB to flush dirty pages from the buffer pool even when the percentage of dirty pages is smaller than innodb_max_dirty_pages_pct.

The patch adds or changes the following commands:

The patch adds extended information about I/O thread status into the SHOW INNODB STATUS output. For each thread, it adds the following items:

• The number of reads, writes, and requests served
• Total time spent performing I/O operations (in seconds)
• Average time to complete I/O (in milliseconds)
• Maximum I/O wait time (in milliseconds)

The following sample illustrates the new information:

--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread) reads 12 writes 0 requests 2 io secs 0.012104 io msecs/request 6.052000 max_io_wait 7.802000
I/O thread 1 state: waiting for i/o request (log thread) reads 0 writes 47 requests 47 io secs 0.013584 io msecs/request 0.289021 max_io_wait 12.548000
I/O thread 2 state: waiting for i/o request (read thread) reads 15429 writes 0 requests 253 io secs 25.814333 io msecs/request 102.032937 max_io_wait 1380.277000
I/O thread 3 state: waiting for i/o request (write thread) reads 0 writes 11079 requests 184 io secs 7.083663 io msecs/request 38.498168 max_io_wait 170.856000
I/O thread 4 state: waiting for i/o request (write thread) reads 0 writes 10252 requests 167 io secs 10.359212 io msecs/request 62.031210 max_io_wait 250.170000
I/O thread 5 state: waiting for i/o request (write thread) reads 0 writes 195 requests 7 io secs 0.185287 io msecs/request 26.469571 max_io_wait 63.668000
I/O thread 6 state: waiting for i/o request (write thread) reads 0 writes 170 requests 7 io secs 0.286308 io msecs/request 40.901143 max_io_wait 81.230000
I/O thread 7 state: waiting for i/o request (write thread) reads 0 writes 79 requests 4 io secs 0.107899 io msecs/request 26.974750 max_io_wait 38.503000
I/O thread 8 state: waiting for i/o request (write thread) reads 0 writes 113 requests 5 io secs 0.136199 io msecs/request 27.239800 max_io_wait 59.599000
I/O thread 9 state: waiting for i/o request (write thread) reads 0 writes 135 requests 4 io secs 0.141793 io msecs/request 35.448250 max_io_wait 75.142000
I/O thread 10 state: waiting for i/o request (write thread) reads 0 writes 112 requests 3 io secs 0.124129 io msecs/request 41.376333 max_io_wait 54.062000