MySQL 8.4 Release Notes
InnoDB 主线程和其他线程在后台执行各种任务，其中大多数与 I/O 相关，例如将脏页从缓冲池中刷新以及将变更缓冲区的更改写入相应的二级索引。InnoDB 试图以不影响服务器正常运行的方式执行这些任务。它会尝试估计可用的 I/O 带宽，并调整其活动以利用可用容量。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.
innodb_io_capacity变量定义了InnoDB可用的总体 I/O 容量。它应该设置为系统每秒可以执行的 I/O 操作（IOPS）数的近似值。当设置innodb_io_capacity时，InnoDB会根据设置的值估算可用于后台任务的 I/O 带宽。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.
您可以将 innodb_io_capacity 设置为 100 或更高的值。默认值为 10000。通常，更快的硬盘、RAID 配置和固态硬盘 (SSD) 相比低端存储设备（如最高 7200 RPM 的硬盘）更适合使用更高的值。You can set innodb_io_capacity to
a value of 100 or greater. The default value is
10000. Typically, faster hard drives, RAID
configurations, and solid state drives (SSDs) benefit from higher
values than do lower-end storage devices, such as hard drives up
to 7200 RPMs.
理想情况下，将设置保持在尽可能低的水平，但不要低到以至于后台活动落后。如果值过高，数据会从缓冲池和更改缓冲区过快地移除，导致缓存无法提供显著的好处。对于能够处理更高 I/O 速率的繁忙系统，可以设置更高的值，以帮助服务器处理与高行更改速率相关的后台维护工作。通常，你可以根据用于 InnoDB I/O 的驱动器数量来增加该值。例如，在使用多个磁盘或 SSD 的系统上，可以增加该值。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.
虽然你可以指定一个像一百万这样的大值，但实际上如此大的值几乎没有什么益处。通常，不推荐将值设置超过 20000，除非你确定较低的值无法满足你的工作负载。另请参阅innodb_io_capacity_max选项，当刷新落后时，它会自动增加该值。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. See also the
innodb_io_capacity_max option
that automatically increases this value when flushing falls
behind.
在调整innodb_io_capacity时，请考虑写入工作负载。写入工作负载大的系统可能会从较高的设置中受益。写入工作负载小的系统，较低的设置可能就足够了。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.
innodb_io_capacity 设置不是针对每个缓冲池实例的设置。可用的 I/O 容量在缓冲池实例之间平均分配，用于刷新活动。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.
您可以在 MySQL 选项文件（my.cnf 或 my.ini）中设置 innodb_io_capacity 值，也可以在运行时使用 SET GLOBAL 语句修改它，这需要具有足够权限来设置全局系统变量。请参阅 第 7.1.9.1 节，“系统变量权限”。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”.
innodb_flush_sync 变量默认启用，会导致在发生检查点处的 I/O 突发活动时，innodb_io_capacity 设置被忽略。要遵循 innodb_io_capacity 和 innodb_io_capacity_max 定义的 I/O 速率，请禁用 innodb_flush_sync。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.
您可以在 MySQL 选项文件（my.cnf 或 my.ini）中设置 innodb_flush_sync 值，或者使用 SET
GLOBAL 语句在运行时修改它，这需要有足够的权限来设置全局系统变量。请参阅 第 7.1.9.1 节，“系统变量权限”。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”.
如果刷新活动滞后，InnoDB 可以更积极地刷新，其每秒 I/O 操作数 (IOPS) 高于 innodb_io_capacity 变量定义的值。innodb_io_capacity_max 变量定义了在这种情况下 InnoDB 后台任务执行的最大 IOPS 数。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.
如果在启动时指定了 innodb_io_capacity 设置，但未为 innodb_io_capacity_max 指定值，则 innodb_io_capacity_max 默认设置为 innodb_io_capacity 的两倍。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.
在配置 innodb_io_capacity_max 时，通常以 innodb_io_capacity 的两倍作为起点是一个不错的选择。与 innodb_io_capacity 设置一样，应尽量保持设置值较低，但不要低到让 InnoDB 无法在 innodb_io_capacity 设置之上充分提高 IOPS 的速率。When configuring
innodb_io_capacity_max, twice
the innodb_io_capacity is often
a good starting point. 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.
在调整 innodb_io_capacity_max 时，请考虑写入工作负载。具有大量写入工作负载的系统可能会从较高的设置中受益。对于写入工作负载较小的系统，较低的设置可能就足够。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 不能设置为低于 innodb_io_capacity 值的数值。innodb_io_capacity_max cannot
be set to a value lower than the
innodb_io_capacity value.
设置innodb_io_capacity_max到DEFAULT using a
SET
statement (SET GLOBAL
innodb_io_capacity_max=DEFAULT) sets
innodb_io_capacity_max to the
default value. Before MySQL 8.4, this set it to the maximum
value instead of the default 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
default value. Before MySQL 8.4, this set it to the maximum
value instead of the default value.
innodb_io_capacity_max 限制适用于所有缓冲池实例。这不是针对每个缓冲池实例的设置。The innodb_io_capacity_max
limit applies to all buffer pool instances. It is not a per
buffer pool instance setting.