Tuning innodb_buffer_pool_chunk_size variable

Basic Details

The innodb_buffer_pool_chunk_size variable sets the chunk size for InnoDB buffer pool resizing operations. Tuning this setting may change the buffer pool size.

innodb_buffer_pool_chunk_size – Usage

innodb_buffer_pool_chunk_size is a variable closely related to innodb_buffer_pool_size and innodb_buffer_pool_instances.

The buffer pool is reduced or enlarged by the chunk size set by innodb_buffer_pool_chunk size. Within the buffer pool, there can be multiple instances set by innodb_buffer_pool_instances. Instances are divided into chunks.
InnoDB buffer pool and chunks
Consider a server situation with 4GB of RAM:

Notice how each variable has an impact on the succeeding variable.

innodb_buffer_pool_chunk_size – Configuration

When configuring innodb_buffer_pool_chunk_size, the server will need to be restarted. The innodb_buffer_pool_chunk_size is best determined, by using a simple formula, and then rounding to the nearest 1MB:

Innodb_buffer_pool_chunk_size = Innodb_buffer_pool_size/innodb_bufffer_pool_instances

Command Line:
mysqld> set innodb_buffer_pool_chunk_size = XX

Replace XX with value to suit your database needs. This change will only be applied to new connections. Make a new connection to the server and the values will update. SET GLOBAL will not persist through a server restart.

Configuration File:
innodb_buffer_pool_chunk_size = XX

Replace XX with value to suit your database needs. Must restart MySQL server to see changes applied.

innodb_buffer_pool_chunk_size Considerations

Note: Innodb_buffer_pool_size will be adjusted automatically to the value or a multiple of the value of innodb_buffer_pool_instances * innodb_buffer_pool_size.

When optimizing for the best innodb_buffer_pool_chunk_size value, aim for chunk size to be 2 to 5% of the buffer pool size. If system memory is increased, work from the rule that 50 to 75% of RAM can be allocated for innodb_buffer_pool_size, and work backward to determine innodb_buffer_pool_chunk_size needs to be updated.
Releem's suite of tools can calculate and automatically set innodb_buffer_pool_chunk_size to the optimum value. No equations or manual adjustments are needed on your part. Your server's MySQL Performance Score will rise with Releem's configuration recommendations.
Ready to dive in?
Try Releem today for FREE! No credit card required.