How Many innodb_buffer_pool_instances Do You Need in MySQL 8?

Author: Vadim Tkachenko Translation: Guan Changlong

In previous articles ( MySQL 5.7 Performance Tuning After Installation and MySQL 101: Tuning MySQL Performance Parameters ) I recommended using innodb_buffer_pool_instances = 8 , but I could not confirm whether this value is optimal. This post explores the results of using different innodb_buffer_pool_instances values under specific conditions.

I will use the sysbench oltp_read_write benchmark and set innodb_buffer_pool_size = 25GB for a 100 GB database, creating competition for buffer‑pool space and an I/O‑intensive scenario.

Benchmark hardware configuration:

System | Supermicro; SYS-F619P2-RTN; v0123456789 (Other)
Platform | Linux
Release | Ubuntu 18.04.4 LTS (bionic)
Kernel | 5.3.0-42-generic
Architecture | CPU = 64-bit, OS = 64-bit
Threading | NPTL 2.27
SELinux | No SELinux detected
Virtualized | No virtualization detected
# Processor ##################################################
Processors | physical = 2, cores = 40, virtual = 80, hyperthreading = yes
Models | 80xIntel(R) Xeon(R) Gold 6230 CPU @ 2.10GHz
Caches | 80x28160 KB
# Memory #####################################################
Total | 187.6G

Data resides on a SATA SSD (Intel SSDSC2KB960G8, enterprise‑grade D3‑S4510).

Brief setup overview:

Data cannot be fully cached in memory (data size ≈100 GB, server memory 188 GB, but innodb_buffer_pool_size is limited to 25 GB via O_DIRECT ).

Workload is read‑write intensive, fully ACID‑compliant with data safety enabled.

For the SATA SSD, innodb_io_capacity is set to 2000 and innodb_io_capacity_max to 4000.

Tested innodb_buffer_pool_instances values: 1, 2, 4, 8, 16, 32, 64 (the maximum allowed by MySQL).

Test command:

sysbench oltp_read_write --threads=150 --time=10000 \
--tables=40 --table_size=10000000 --mysql-host=127.0.0.1 \
--mysql-user=sbtest --mysql-password=sbtest \
--max-requests=0 --report-interval=1 --mysql-db=sbtest \
--mysql-ssl=off --create_table_options=DEFAULT CHARSET=utf8mb4 \
--report_csv=yes --rand-type=pareto run

The benchmark runs for three hours, reporting throughput every second.

Results on SATA SSD

innodb_buffer_pool_instances = 1

innodb_buffer_pool_instances = 2

innodb_buffer_pool_instances = 4

innodb_buffer_pool_instances = 8

innodb_buffer_pool_instances = 16

innodb_buffer_pool_instances = 32

innodb_buffer_pool_instances = 64

It is evident that increasing innodb_buffer_pool_instances positively impacts throughput. A combined chart (shown below) highlights the trend.

Comparing throughput and variance over the last 2500 seconds further confirms the pattern.

Thus, innodb_buffer_pool_instances = 64 delivers the best throughput with the smallest variability. From a variability standpoint, the previously suggested value of 8 is better than 1‑4, but it does not achieve the highest throughput.

Final thoughts

For this particular workload, innodb_buffer_pool_instances = 64 is optimal, though a reliable method to determine the best value is not yet established. Values of 1‑4 cause high variability or even stalls, so starting with 8 is a reasonable compromise.

Related links

MySQL 5.7 Performance Tuning After Installation

MySQL 101: Parameters to Tune for MySQL Performance