innodb_io_capacity – determines the number of write IOPS for background InnoDB tasks, such as flushing pages from a buffer pool, etc. If the cleanup operation is lagging, then InnoDB can perform cleanup more aggressively at a higher IOPS, which is defined in the innodb_io_capacity_max variable.
Let’s see the current value of innodb_io_capacity (default 200):
show variables like 'innodb_io_capacity';
You can determine the performance of the disk system, for example, as I described in these articles:
Test iops using fio
DiskSpd. Windows IOPS Test
It is imperative to measure it by simultaneously performing random writes and random reads, since in this case the disk performance is much lower than with separate tests.
For example, you can specify about 50% of the available IOPS, or at least slightly increase the value, for example:
SET global innodb_io_capacity=1000;
And also we will indicate in the configuration file so that the value is not reset after restarting the MySQL server:
[mysqld]
innodb_io_capacity=1000
Let’s see the current value of innodb_io_capacity_max (default 2000):
show variables like 'innodb_io_capacity_max';
Here’s an example of a change:
SET global innodb_io_capacity_max=4000;
We will also indicate in the configuration file:
[mysqld]
innodb_io_capacity_max=4000
See also my other articles about MySQL