InnoDB master thread and other background
threads perform various tasks in the background, most of which are
I/O related, such as flushing dirty pages from the buffer pool or
writing changes from the insert buffer to the appropriate
InnoDB attempts to perform
these tasks in a way that does not adversely affect the normal
working of the server. It tries to estimate the free I/O bandwidth
available and tune its activities to take advantage of this free
InnoDB used a hard
coded value of 100 IOPs (input/output operations per second)
as the total I/O capacity of the server.
defines the overall I/O capacity available to
InnoDB. It should be set to approximately the
number of I/O operations that the system can perform per second.
The value depends on your system configuration. When
innodb_io_capacity is set,
InnoDB estimates the I/O bandwidth available
for background tasks based on the set value. Setting the value to
100 reverts to the old behavior.
You can set
a value of 100 or greater. The default value is
200, reflecting that the performance of typical
modern I/O devices is higher than in the early days of MySQL.
Typically, values around the previous default of 100 are
appropriate for consumer-level storage devices, such as hard
drives up to 7200 RPMs. Faster hard drives, RAID configurations,
and SSDs benefit from higher values.
You can set the
innodb_io_capacity value in the
MySQL option file (
my.ini) or change it dynamically with the
statement, which requires privileges sufficient to set global
system variables. See
Section 18.104.22.168, “System Variable Privileges”.
For related information, see Section 8.5.8, “Optimizing InnoDB Disk I/O”.