- innodb_read_io_threads - the number of threads for read prefetch requests made by InnoDB. Only 1 thread was used prior to this patch.
- innodb_write_io_threads - the number of threads for writing dirty pages. Only 1 thread was used prior to this patch. If InnoDB is run with O_DIRECT then it usually helps to make this value larger than 1.
- innodb_max_merge_io - the maximum number of requests for adjacent pages to merge into one large IO operation. The default is 64. You don't need to change this, but if you do, please post the results.
The output from the FILE I/O section of SHOW INNODB STATUS has been extended to include statistics on IO operations. There is also one line per IO thread. The added output includes:
- reads, writes - the number of read or write requests done by this thread. One request is for one 16kb Innodb page except for the log thread.
- requests - the number of read or write system calls used. As reads and writes for adjacent pages may be merged into one IO, the number of requests is always less than or equal to the number of reads and writes.
- io secs - the number of seconds to process the reads and writes.
- io msecs/request - average time for an IO request in milliseconds, (io secs * 1000) / (requests)
- max_io_wait - the maximum time in milliseconds for an IO operation
-------- FILE I/O -------- I/O thread 0 state: waiting for i/o request (insert buffer thread) reads 0 writes 0 requests 0 io secs 0.000000 io msecs/request 0.000000 max_io_wait 0.000000 I/O thread 1 state: waiting for i/o request (log thread) reads 0 writes 30 requests 30 io secs 0.000570 io msecs/request 0.019000 max_io_wait 0.043000 I/O thread 2 state: waiting for i/o request (read thread) reads 414 writes 0 requests 36 io secs 0.004601 io msecs/request 0.127806 max_io_wait 0.210000 I/O thread 3 state: waiting for i/o request (read thread) reads 337 writes 0 requests 26 io secs 0.003806 io msecs/request 0.146385 max_io_wait 0.298000 I/O thread 10 state: waiting for i/o request (write thread) reads 0 writes 1137 requests 75 io secs 0.022748 io msecs/request 0.303307 max_io_wait 5.213000 I/O thread 11 state: waiting for i/o request (write thread) reads 0 writes 259 requests 11 io secs 0.005803 io msecs/request 0.527545 max_io_wait 2.524000
The state of the 5.0 branch after the latest patch:
show patches; File Name Version Author License Comment show_patches.patch SHOW PATCHES 1.0 Jeremy Cole N/A innodb_io_threads.patch INNODB IO THREADS 1.0 email@example.com GPL More background IO threads innodb_fast_mutex.patch INNODB FAST MUTEX 1.0 firstname.lastname@example.org GPL Faster Innodb mutex innodb_io_capacity.patch INNODB IO CAPACITY 1.0 email@example.com GPL Background IO rate limits innodb_disallow_writes.patch INNODB DISALLOW WRITES 1.0 firstname.lastname@example.org GPL Prevent writes to Innodb