The master thread in InnoDB is a thread that performs various tasks in the background. Most of these tasks are I/O related, such as flushing dirty pages from the buffer pool or writing changes from the insert buffer to the appropriate secondary indexes. The master thread 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 capacity. Historically, InnoDB has used a hard coded value of 100 IOPs (input/output operations per second) as the total I/O capacity of the server.
indicates the overall I/O capacity available to InnoDB. This
parameter 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,
the master threads 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 the value of
innodb_io_capacity to any
number 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.
innodb_io_capacity setting is
a total limit for all buffer pool instances. When dirty pages are
limit is divided equally among buffer pool instances. For more
information, see the
You can set the value of this parameter in the MySQL option file
my.ini) or change
it dynamically with the
SET GLOBAL command,
which requires the
configuration option, introduced in MySQL 5.7.8, causes the
innodb_io_capacity setting to be
ignored during bursts of I/O activity that occur at checkpoints.
innodb_flush_sync is enabled by
InnoDB master thread also
performed any needed purge
operations. In MySQL 5.6.5 and higher, those I/O operations are
moved to other background threads, whose number is controlled by
For more information about InnoDB I/O performance, see Section 9.5.8, “Optimizing InnoDB Disk I/O”.