innodb_buffer_pool_instance

8.0 預設值

8 (or 1 if innodb_buffer_pool_size < 1 GiB)

8.4 預設值

更細節的自動設置

Dynamic

需特別關注

簡介

將 innodb_buffer_pool 分成多個單獨的 instance，用來減少線程之間的競爭提高併發性能。

說明

innodb_buffer_pool 使用的總量為 innodb_buffer_pool_size，並分配給 innodb_buffer_pool_instance 個 instance，建議至少每個 instance 要分配到至少 1GB。

預設值的計算變的更為細節，如下：

If innodb_buffer_pool_size <= 1 GiB, then innodb_buffer_pool_instances=1

If innodb_buffer_pool_size > 1 GiB, then this is the minimum value from the following two calculated hints in the range of 1-64:

Buffer pool hint: Calculated as 1/2 of (innodb_buffer_pool_size / innodb_buffer_pool_chunk_size)

CPU hint: Calculated as 1/4 of the number of available logical processors

建議

維持新的預設值即可。

參考

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.