MySQL 5.5 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.
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.5 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 using 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
4.
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.
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.5 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.