====== !!! Patch is deprecated and is not available in percona builds !!! ====== ====== !!! Patch exists only in some special builds !!! ====== ====== Patch: innodb_io_tune ====== 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 [[http://code.google.com/p/google-mysql-tools/wiki/InnodbIoTuning | InnodbIoTuning ]] and [[http://code.google.com/p/google-mysql-tools/wiki/InnodbAsyncIo | InnodbAsyncIo ]] patches. ===== Variables Provided ===== The patch provides the following [[quickref:option_and_variable_reference|variables]]: ==== innodb_read_io_threads ==== | Type|System and command-line variable| | Scope|Global| | Dynamic|No| | Default|1| The number of background I/O threads for read prefetch requests. ==== innodb_write_io_threads ==== | Type|System and command-line variable| | Scope|Global| | Dynamic|No| | Default|1| The number of background I/O threads for writing dirty pages from the buffer pool. ==== innodb_max_merged_io ==== | Type|System and command-line variable| | Scope|Global| | Dynamic|No| | Default|64| The maximum number of I/O requests merged to issue large I/O from background I/O threads. ==== innodb_io_capacity ==== | Type|System and command-line variable| | Scope|Global| | Dynamic|No| | Default|100| 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. ==== innodb_extra_dirty_writes ==== | Type|System and command-line variable| | Scope|Global| | Dynamic|No| | Default|ON| 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''. ===== Commands Changed ===== The patch adds or changes the following [[quickref:command reference|commands]]: ==== SHOW INNODB STATUS ==== 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 ===== Patch Information ===== | Author/Origin|Google, Percona| | Bugs fixed| | | Dependencies| [[ patches:innodb_fsync_source ]] |