MySQL 8.0 Release Notes
MySQL 8.0 Source Code Documentation
The InnoDB master thread and other threads
perform various tasks in the background, most of which are I/O
related, such as flushing dirty pages from the buffer pool and
writing changes from the change buffer to the appropriate
secondary indexes. 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 available I/O
bandwidth and tune its activities to take advantage of available
capacity.
The innodb_io_capacity variable
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
(IOPS). When innodb_io_capacity
is set, InnoDB estimates the I/O bandwidth
available for background tasks based on the set value.
You can set innodb_io_capacity to
a value of 100 or greater. The default value is
200. Typically, values around 100 are
appropriate for consumer-level storage devices, such as hard
drives up to 7200 RPMs. Faster hard drives, RAID configurations,
and solid state drives (SSDs) benefit from higher values.
Ideally, keep the setting as low as practical, but not so low that
background activities fall behind. If the value is too high, data
is removed from the buffer pool and change buffer too quickly for
caching to provide a significant benefit. For busy systems capable
of higher I/O rates, you can set a higher value to help the server
handle the background maintenance work associated with a high rate
of row changes. Generally, you can increase the value as a
function of the number of drives used for
InnoDB I/O. For example, you can increase the
value on systems that use multiple disks or SSDs.
The default setting of 200 is generally sufficient for a lower-end SSD. For a higher-end, bus-attached SSD, consider a higher setting such as 1000, for example. For systems with individual 5400 RPM or 7200 RPM drives, you might lower the value to 100, which represents an estimated proportion of the I/O operations per second (IOPS) available to older-generation disk drives that can perform about 100 IOPS.
Although you can specify a high value such as a million, in practice such large values have little benefit. Generally, a value higher than 20000 is not recommended unless you are certain that lower values are insufficient for your workload.
Consider write workload when tuning
innodb_io_capacity. Systems with
large write workloads are likely to benefit from a higher setting.
A lower setting may be sufficient for systems with a small write
workload.
The innodb_io_capacity setting is
not a per buffer pool instance setting. Available I/O capacity is
distributed equally among buffer pool instances for flushing
activities.
You can set the
innodb_io_capacity value in the
MySQL option file (my.cnf or
my.ini) or modify it at runtime using a
SET GLOBAL
statement, which requires privileges sufficient to set global
system variables. See
Section 7.1.9.1, “System Variable Privileges”.
The innodb_flush_sync variable,
which is enabled by default, causes the
innodb_io_capacity setting to
be ignored during bursts of I/O activity that occur at
checkpoints. To adhere to
the I/O rate defined by the
innodb_io_capacity and
innodb_io_capacity_max
settings, disable
innodb_flush_sync.
You can set the
innodb_flush_sync value in the
MySQL option file (my.cnf or
my.ini) or modify it at runtime using a
SET
GLOBAL statement, which requires privileges sufficient
to set global system variables. See
Section 7.1.9.1, “System Variable Privileges”.
If flushing activity falls behind, InnoDB can
flush more aggressively, at a higher rate of I/O operations per
second (IOPS) than defined by the
innodb_io_capacity variable.
The innodb_io_capacity_max
variable defines a maximum number of IOPS performed by
InnoDB background tasks in such situations.
If you specify an
innodb_io_capacity setting at
startup but do not specify a value for
innodb_io_capacity_max,
innodb_io_capacity_max defaults
to twice the value of
innodb_io_capacity or 2000,
whichever value is greater.
When configuring
innodb_io_capacity_max, twice
the innodb_io_capacity is often
a good starting point. The default value of 2000 is intended for
workloads that use an SSD or more than one regular disk drive. A
setting of 2000 is likely too high for workloads that do not use
SSDs or multiple disk drives, and could allow too much flushing.
For a single regular disk drive, a setting between 200 and 400
is recommended. For a high-end, bus-attached SSD, consider a
higher setting such as 2500. As with the
innodb_io_capacity setting,
keep the setting as low as practical, but not so low that
InnoDB cannot sufficiently extend rate of
IOPS beyond the
innodb_io_capacity setting.
Consider write workload when tuning
innodb_io_capacity_max. Systems
with large write workloads may benefit from a higher setting. A
lower setting may be sufficient for systems with a small write
workload.
innodb_io_capacity_max cannot
be set to a value lower than the
innodb_io_capacity value.
Setting innodb_io_capacity_max
to DEFAULT using a
SET
statement (SET GLOBAL
innodb_io_capacity_max=DEFAULT) sets
innodb_io_capacity_max to the
maximum value.
The innodb_io_capacity_max
limit applies to all buffer pool instances. It is not a per
buffer pool instance setting.