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.
Releem automatically tunes innodb_buffer_pool_chunk_size and 44 other variables to improve MySQL performance. Try Releem for Free, or deepen your understanding by reading our detailed article.
Releem automatically tunes innodb_buffer_pool_chunk_size and 44 other variables to improve MySQL performance. Try Releem for Free, or deepen your understanding by reading our detailed article.
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.
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:
Notice how each variable has an impact on the succeeding variable.
- 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 = 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_chunk_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.
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 Releem Score will rise with Releem's configuration recommendations.