]]>
]]>

!!! 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 InnodbIoTuning and InnodbAsyncIo patches.

Variables Provided

The patch provides the following variables:

innodb_read_io_threads

TypeSystem and command-line variable
ScopeGlobal
DynamicNo
Default1

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

innodb_write_io_threads

TypeSystem and command-line variable
ScopeGlobal
DynamicNo
Default1

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

innodb_max_merged_io

TypeSystem and command-line variable
ScopeGlobal
DynamicNo
Default64

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

innodb_io_capacity

TypeSystem and command-line variable
ScopeGlobal
DynamicNo
Default100

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

TypeSystem and command-line variable
ScopeGlobal
DynamicNo
DefaultON

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 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/OriginGoogle, Percona
Bugs fixed
Dependencies innodb_fsync_source
 
patches/innodb_io_tune.txt · Last modified: 2011/07/17 21:05 (external edit)
Except where otherwise noted, content on this wiki is licensed under the following license:CC Attribution-Noncommercial-Share Alike 3.0 Unported
Contact Us 24 Hours A Day
SupportContact us 24×7
Emergency? Contact us for help now!
Sales North America(888) 316-9775 or
(208) 473-2904
Sales
Europe
+44-208-133-0309 (UK)
0-800-051-8984 (UK)
0-800-181-0665 (GER)
Training(855) 55TRAIN or
(925) 271-5054

 

Share This
]]> ]]>