innodb_buffer_pool_chunk_size: Configure Buffer Pool Chunk Size

Summary: in this tutorial, you will learn how to configure the chunk size of the buffer pool innodb_buffer_pool_chunk_size in the InnoDB storage engine.

Introduction to buffer pool chunk size configuration variable innodb_buffer_pool_chunk_size

The buffer pool in InnoDB is an in-memory structure that stores the frequently accessed data.

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

The innodb_buffer_pool_size configuration variable defines the size of the buffer pool.

If the buffer pool is large (multi-gigabyte), you can enable multiple buffer pools using the innodb_buffer_pool_instances configuration variable to improve the concurrency.

The innodb_buffer_pool_chunk_size is a configuration option that determines the chunk size used when resizing the buffer pool.

The innodb_buffer_pool_chunk_size indirectly affects the buffer pool size, as the total buffer pool size is a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances.

In MySQL 8.0, the default value of the innodb_buffer_pool_chunk_size is 128MB. Its valid range is 1MB to 512MB. To avoid performance issues, the number of chunks should not exceed 1000.

MySQL only allows you to set the innodb_buffer_pool_chunk_size at startup or in the configuration file. In other words, you cannot use the SET statement to assign it a value while the MySQL server is running.

When setting the innodb_buffer_pool_chunk_size configuration variable, the innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances should not exceed the innodb_buffer_pool_size. If it does, MySQL will truncate the value to innodb_buffer_pool_size / innodb_buffer_pool_instances.

If you change innodb_buffer_pool_chunk_size, MySQL automatically adjust innodb_buffer_pool_size to equal or a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances during buffer pool initialization:

innodb_buffer_pool_size = n * (innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances)Code language: SQL (Structured Query Language) (sql)

Checking innodb_buffer_pool_chunk_size value

First, connect to the MySQL server:

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

Second, the value of the innodb_buffer_pool_chunk_size variable:

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

Output:

+-------------------------------+-----------+
| Variable_name                 | Value     |
+-------------------------------+-----------+
| innodb_buffer_pool_chunk_size | 134217728 |
+-------------------------------+-----------+
1 row in set (0.04 sec)Code language: SQL (Structured Query Language) (sql)

The chunk size is in bytes. By default, the innodb_buffer_pool_chunk_size is 134217728 bytes, which is 128MB.

This value is valid because the pool size is 32 times of product of the chunk size and the number of buffer instances:

SELECT @@innodb_buffer_pool_size / @@innodb_buffer_pool_chunk_size * @@innodb_buffer_pool_instances multiple;Code language: SQL (Structured Query Language) (sql)

Output:

+----------+
| multiple |
+----------+
|  32.0000 |
+----------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

Configure innodb_buffer_pool_chunk_size

The set a value for the innodb_buffer_pool_chunk_size, you need to change it in the MySQL configuration file:

[mysqld]
innodb_buffer_pool_chunk_size=134217728Code language: SQL (Structured Query Language) (sql)

Note that after changing the configuration file, you need to restart the MySQL server for the changes to take effect.

Alternatively, you use it as the command line argument at the MySQL server startup:

mysqld --innodb-buffer-pool-chunk-size=134217728Code language: SQL (Structured Query Language) (sql)

Summary

• Use the innodb_buffer_pool_chunk_size to set the chunk size used when resizing the InnoDB buffer pool.