innodb_buffer_pool_instances: Configuring Multiple Buffer Pools

Summary: in this tutorial, you will learn how to use the innodb_buffer_pool_instances configuration option to configure multiple buffer pool instances for improved concurrency.

Buffer pool & concurrency

In InnoDB, a buffer pool is a memory structure that stores the frequently accessed data and indexes.

InnoDB utilizes the buffer pool to improve the performance of the MySQL database server by accessing data in memory directly instead of reading data from disk.

Typically, the more memory you allocate to the buffer pool, the better performance of the MySQL server is.

To set an optimal size of the buffer pool, you adjust the innodb_buffer_pool_size configuration variable.

When the buffer pool is large (multi-gigabyte range), MySQL serves many data requests by retrieving them from memory. In this case, you might encounter a bottleneck from multiple threads attempting to access the buffer pool at the same time.

To minimize the contention, you can enable multiple buffer pools. The innodb_buffer_pool_instances configuration variable allows you to specify the number of buffer pool instances.

By default, the innodb_buffer_pool_instances is set to 1. To enable multiple buffer pool instances, you set the innodb_buffer_pool_instances to a value greater than 1. You can set it up to 64.

Note that the innodb_buffer_pool_instances takes effect only when you set innodb_buffer_pool_size to a size of 1GB or more.

Configuring buffer pool instances

First, open the MySQL configuration file (typically my.ini or my.cnf file) in a text editor and set the innodb_buffer_pool_instances to a number, such as 2:

[mysqld]
innodb_buffer_pool_instances=2Code language: SQL (Structured Query Language) (sql)

Because mysqld reads innodb_buffer_pool_instances, you need to place the innodb_buffer_pool_instances option under the [mysqld] section.

Next, verify if the innodb_buffer_pool_size configuration variable is set to a value that is greater than 1GB:

[mysqld]
innodb_buffer_pool_size=2GCode language: SQL (Structured Query Language) (sql)

Then, save the configuration file and restart MySQL.

After that, connect to the MySQL server:

mysql -u root -pCode language: SQL (Structured Query Language) (sql)

Finally, verify the innodb_buffer_pool_instances configuration option:

SHOW VARIABLES LIKE 'innodb_buffer_pool_instances';Code language: SQL (Structured Query Language) (sql)

Output:

+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| innodb_buffer_pool_instances | 2     |
+------------------------------+-------+
1 row in set (0.01 sec)Code language: SQL (Structured Query Language) (sql)

Summary

Enable multiple buffer pool instances to significantly enhance the concurrency and overall performance of MySQL servers with large buffer pools.
Use the innodb_buffer_pool_instances configuration variable to set multiple buffer pool instances for improved concurrency.

Was this tutorial helpful?