innodb_buffer_pool_chunk_size

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.

Consider a server situation with 4GB of RAM:

• innodb_buffer_pool_size set to 2GB
• innodb_buffer_pool_instances set to 10 instances
• innodb_buffer_pool_chunk_size set to the default 128MB with 2 chunks per instance (20 chunks)

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:
[mysqld]
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.