Skip to main content
Enterprise applications

innodb_io_capacity

Contributors jfsinmsp
PDFs

In the InnoDB plug-in, a new parameter called innodb_io_capacity was added from MySQL 5.7.

It controls the maximum number of IOPS that InnoDB performs (which includes the flushing rate of dirty pages as well as the insert buffer [ibuf] batch size). The innodb_io_capacity parameter sets an upper limit on IOPS by InnoDB background tasks, such as flushing pages from the buffer pool and merging data from the change buffer.

Set the innodb_io_capacity parameter to the approximate number of I/O operations that the system can perform per second. Ideally, keep the setting as low as possible, but not so low that background activities slow down. If the setting is too high, data is removed from the buffer pool and insert buffer too quickly for caching to provide a significant benefit.

Tip NetApp recommends that if using this setting over NFS, analyzing the test result of IOPS (SysBench/Fio) and set the parameter accordingly. Use the smallest value possible for flushing and purging to keep up unless you see more modified or dirty pages than you want in the InnoDB buffer pool.
Caution Do not use extreme values such as 20,000 or more unless you've proved that lower values are not sufficient for your workload.

The Innodb_IO_capacity parameter regulates flushing rates and related I/O.

Caution You can seriously harm performance by setting this parameter or the innodb_io_capacity_max parameter too high and wasting I/O operations with premature flushing.