Benchmarking InnoDB Buffer Pool Instances for a 140 GB MySQL 8 Database

This article repeats a previous benchmark with a larger 140 GB database (innodb_buffer_pool_size=140G) on MySQL 8 to evaluate how many innodb_buffer_pool_instances are needed for a CPU‑intensive workload.

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

The server uses a SATA SSD (Intel SSDSC2KB960G8) for storage.

Brief setup overview

Data size fits in memory (≈100 GB data, 188 GB RAM, 140 GB allocated to innodb_buffer_pool_size).

Workload is read/write intensive with full ACID compliance. innodb_io_capacity set to 2000 and innodb_io_capacity_max to 4000 for SATA SSD.

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

Throughput graphs for each innodb_buffer_pool_instances value are shown below:

Overall, throughput improves as the number of instances increases, with the best and most stable performance observed at 64 instances; increasing beyond 64 yields no additional benefit.

When comparing the last 2500 seconds of the run, the 64‑instance configuration shows the highest throughput and the smallest variance, while the 32‑instance run still exhibits noticeable pauses.

From 1 to 8 instances, regular pauses occur, sometimes dropping to 0 tps for a few seconds.

Final thoughts

For this specific hardware (80‑core CPU) and workload, innodb_buffer_pool_instances=64 provides the optimal performance, but the ideal value depends on the underlying hardware and the characteristics of the workload.