Observing the Performance Impact of innodb_buffer_pool_instances in MySQL

We introduce the MySQL configuration parameter innodb_buffer_pool_instances , which splits the InnoDB buffer pool into multiple instances each protected by its own lock, thereby reducing lock contention and potentially improving performance.

The experiment starts by creating an empty database and increasing performance_schema_events_waits_history_long_size to capture more wait events. Sysbench is used to load data, followed by a 60‑second warm‑up period to stabilize performance.

Performance_schema is configured to enable only the hash_table_locks instrument (producer) and the related wait collection (consumer). Observations are filtered to exclude background MySQL threads such as the flush‑page thread.

After clearing previous observations, sysbench runs for another 60 seconds while the performance_schema records over one million rows of hash_table_locks data. The average, 90th percentile, and 99th percentile lock wait times are extracted and tabulated for three configurations: innodb_buffer_pool_instances = 1, 2, 4 .

Key findings include:

The average wait time is dominated by a few extremely long waits, as indicated by the average being higher than the 99th percentile.

Increasing the number of buffer‑pool instances reduces the severity of these extreme waits.

For the tested workload, the 90th and 99th percentile latencies are relatively unchanged, suggesting most SQL statements are not affected by the instance count.

All wait‑time measurements are reported in CPU cycles; 1 cycle equals 1/2,387,771,144 seconds. For innodb_buffer_pool_instances = 1 , the average lock acquisition time is about 6,535,546 cycles (~2.7 ms).

The article emphasizes that the experiment is intended to illustrate the methodology rather than provide definitive performance conclusions, as results can vary with different workloads, hardware, and MySQL configurations.

Readers are encouraged to apply the same observation technique to their own environments to determine the optimal innodb_buffer_pool_instances setting.