innodb_io_capacity_max

8.0 預設值

2 * innodb_io_capacity, with a minimum default value of 2000

8.4 預設值

2 * innodb_io_capacity

Dynamic

Yes

需特別關注

簡介

設置當 flush 滯後時，更積極 flush 下最大的 IOPS。

說明

用於設置當 flush 滯後時，用高於 innodb_io_capacity 的 IOPS 更積極的 flush 的 IOPS 最大值。

應該幫此參數設置的剛好：

過高的設置：導致 buffer pool 頻繁的被 flush，降低快取的 buffer 的好處

過低的設置：導致刷 dirty page 過慢，容易觸發 checkpoint

如果是寫多的場景可以設置較高的值，讀多則設置較低的值。

不能設置低於 innodb_io_capacity。

注意：當 innodb_flush_sync 開啟(預設值) 時，當觸發 checkpoint 的時候會忽略 innodb_io_capacity 和 innodb_io_capacity_max 的設置。

建議

一般照著預設值為 innodb_io_capacity * 2 就差不多了。

參考

MySQL :: MySQL 8.4 Reference Manual :: 17.14 InnoDB Startup Options and System Variables

System variables that are true or false can be enabled at server startup by naming them, or disabled by using a --skip- prefix. For example, to enable or disable the InnoDB adaptive hash index, you can use --innodb-adaptive-hash-index or --skip-innodb-adaptive-hash-index on the command line, or innodb_adaptive_hash_index or skip_innodb_adaptive_hash_index in an option file.

MySQL :: MySQL 8.4 Reference Manual :: 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.