How Many innodb_buffer_pool_instances Do You Need in MySQL 8?

Following up on my recent benchmark posts on MySQL and MariaDB, MySQL and MariaDB on Enterprise SSD Storage and How MySQL and MariaDB Perform on NVMe Storage, I wanted to dig a little deeper and understand how different MySQL parameters affect performance.

One of the obscure MySQL Parameters (in my opinion) is innodb_buffer_pool_instances. In particular, I do not have a feel for how many you need and how to understand if a chosen value is a good or a bad one.

In our articles MySQL 5.7 Performance Tuning After Installation and MySQL 101: Parameters to Tune for MySQL Performance we recommended to use value “8”, but I can’t really say if this is a good enough value. So let’s see what results we will get with different innodb_buffer_pool instances, under the following scenario:

I will use the sysbench oltp_read_write benchmark with pareto distribution. I will set innodb_buffer_pool_size=25GB for a database 100 GB in size, so there will be a competition for buffer_pool space and this will be an IO-bound scenario.

Benchmark

The hardware I use is:

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

With the storage on SATA SSD INTEL SSDSC2KB960G8 (Intel Enterprise-grade SSD D3-S4510).

The short settings overview:

• Data does not fit into memory (the datasize is ~100GB, memory on the server is 188GB, and we allocate 25GB for MySQL innodb_buffer_pool_size using O_DIRECT, so even there is a lot of memory on the server, it is not used over specified 25GB).
• The workload on storage will be very read-write-intensive (reads will be done from the storage), with full ACID-compliant and data safe settings in MySQL.
• For SATA SSD storage  innodb_io_capacity, I will use 2000 and innodb_io_capacity_max = 4000.
• I will test the following values for innodb_buffer_pool_instances: 1, 2, 4, 8, 16, 32, 64.
• Innodb_buffer_pool_instances = 64 is the maximum value allowed by MySQL.

The benchmark command line is:

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

This means that the benchmark will run for three hours with reporting throughput every 1 sec. I will use a three-hour time frame for a reason which will be apparent later from the results.

Results on SATA SSD

Let’s see what results I’ve got individually for each of innodb_buffer_pool_instances:

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

What seems apparent is that as we increase innodb_buffer_pool_instances, it has a positive effect on variation in the throughput. We can condense the results in a single chart to see it closely:

And if we want to compare the throughput and deviation, let’s compare the results for the last 2500 sec:

So actually, innodb_buffer_pool_instances=64 showed the best throughput and less variability. Recommended innodb_buffer_pool_instances=8 seems better compared to 1-4 values in a sense of variability, but it does not produce the best throughput.

Final Thoughts

For this particular case, innodb_buffer_pool_instances=64 was the best choice, but I still can’t recommend a reliable way to find what the optimal value is. Small values 1-4 seems to produce a lot of variability or even stalls, so starting from 8 is a good choice.

by Vadim Tkachenko via Percona Database Performance Blog