17.8.7 Configuring InnoDB I/O Capacity (original) (raw)

17.8.7 Configuring InnoDB I/O Capacity

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 toInnoDB. 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 is10000. 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.

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 forInnoDB I/O. For example, you can increase the value on systems that use multiple disks or SSDs.

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 theinnodb_io_capacity_max option that automatically increases this value when flushing falls behind.

Consider write workload when tuninginnodb_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 theinnodb_io_capacity value in the MySQL option file (my.cnf ormy.ini) or modify it at runtime using aSET GLOBAL statement, which requires privileges sufficient to set global system variables. SeeSection 7.1.9.1, “System Variable Privileges”.