MySQL 5.4 changes to the InnoDB I/O subsystem enable more effective use of available I/O capacity. The changes also provide more control over configuration of the I/O subsystem. Much of this work is based on patches developed by Google, about which more information can be found here:

http://code.google.com/p/google-mysql-tools/wiki/InnodbAsyncIo
http://code.google.com/p/google-mysql-tools/wiki/InnodbIoTuning

Background I/O Threads

InnoDB uses background threads to perform I/O for several kinds of activities, two of which are prefetching disk blocks and flushing dirty pages. Previously, InnoDB used only one thread each to perform these activities, but that can underutilize server capacity. MySQL 5.4 incorporates a Google patch that enables use of multiple background read and write threads, making it possible to read and write pages faster.

The patch makes the number of background I/O threads configurable via system variables: innodb_read_io_threads controls the number of threads to use for read prefetch requests. innodb_write_io_threads controls the number of threads to use for writing dirty pages from the buffer cache to disk. The default for both variables is 8.

The ability to increase the number of I/O threads can benefit systems that use multiple disks for InnoDB. However, the type of I/O being done should be considered. On systems that use buffered writes rather than direct writes, increasing the write thread count higher than 1 might yield little benefit because writes will be quick already.

The patch also introduces the innodb_max_merged_io system variable that specifies the maximum number of background I/O requests that will be merged to issue a larger I/O request in a more contiguous manner. The default value is 64.

Adjustable I/O Rate

Previously, the number of input/output operations per second (IOPS) that InnoDB will perform was a compile-time parameter. The rate was chosen to prevent background I/O from exhausting server capacity and the compiled-in value of 100 reflected an assumption that the server can perform 100 IOPS. However, many modern systems can exceed this, so the value is low and unnecessarily restricts I/O utilization.

MySQL 5.4 incorporates a Google patch that exposes this I/O rate parameter as a system variable, innodb_io_capacity. This variable can be set at server startup, which enables higher values to be selected for systems capable of higher I/O rates. Having a higher I/O rate can help the server handle a higher rate of row changes because it may be able to increase dirty-page flushing, deleted-row removal, and application of changes to the insert buffer. The default value of innodb_io_capacity is 200. In general, you can increase the value as a function of the number of drives used for InnoDB I/O.

The ability to raise the I/O limit should be especially beneficial on platforms that support many IOPS. For example, systems that use multiple disks or solid-state disks for InnoDB are likely to benefit from the ability to control this parameter.

The rate patch also implements an innodb_extra_dirty_writes system variable, which is used in conjunction with innodb_max_dirty_pages_pct. The latter variable specifies the percentage of pages that must be dirty before InnoDB flushes them. If innodb_extra_dirty_writes is enabled (the default), flushing of dirty pages may occur when the server is idle, even if the percentage of dirty pages has not reached the normally required percentage. This can help reduce the amount of flushing that must be done when the server is not idle and has fewer resources to spare. When innodb_extra_dirty_writes is disabled, extra flushing does not occur.