Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 46.8Mb
PDF (A4) - 46.9Mb
PDF (RPM) - 42.3Mb
HTML Download (TGZ) - 10.8Mb
HTML Download (Zip) - 10.8Mb
HTML Download (RPM) - 9.3Mb
Man Pages (TGZ) - 226.1Kb
Man Pages (Zip) - 331.6Kb
Info (Gzip) - 4.2Mb
Info (Zip) - 4.2Mb
Excerpts from this Manual

MySQL 8.0 Reference Manual  /  ...  /  Configuring InnoDB I/O Capacity

15.8.7 Configuring InnoDB I/O Capacity

The 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 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 free I/O bandwidth available and tune its activities to take advantage of this free capacity. Historically, InnoDB used a hard coded value of 100 IOPs (input/output operations per second) as the total I/O capacity of the server.

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. 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 innodb_io_capacity to 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.cnf or my.ini) or change it dynamically with the SET GLOBAL statement, which requires privileges sufficient to set global system variables. See Section, “System Variable Privileges”.

The innodb_flush_sync configuration option 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 default.

For related information, see Section 8.5.8, “Optimizing InnoDB Disk I/O”.