0

I had some complications regarding innodb_buffer_pool_size. I read the official documentation, and I found out the formula innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances.

so if my innodb_buffer_pool_chunk_size is 268435456, and my innodb_buffer_pool_instances is 8, what should be my innodb_buffer_pool_size?

how does this work?

Improve this question
3
  • I want to know innodb_buffer_pool_size parameter's relation with innodb_buffer_pool_instances and innodb_buffer_pool_size.
    – datascinalyst
    Commented Nov 23, 2023 at 13:32
  • How much RAM do you have?
    – Rick James
    Commented Nov 23, 2023 at 19:00
  • 16GB; what'll be the preferred innodb_buffer_pool_size, and if I increase my RAM to 32GB, what'll be the preferred size? How to set innodb_buffer_pool_size size according to my RAM? same goes for innodb_buffer_pool_instance and innodb_buffer_pool_chunk_size.
    – datascinalyst
    Commented Nov 24, 2023 at 5:15

1 Answer 1

Reset to default
2

You don't need to calculate the pool size. Set innodb_buffer_pool_size to the total RAM you want to allocate to the buffer pool.

Let MySQL calculate the pool instances and chunk size. MySQL does this automatically by default.

In theory, you could fine-tune the pools and chunks. But it's almost certain that you don't have to do anything other than the default.

I've managed thousands of MySQL instances, for small instances and multi-terabyte instances, and I've never had to size the chunks and pools manually.

Improve this answer
2
  • Thanks @BillKarwin, for your response. So you're saying if I set the pool_size to = 32G, the innodb_buffer_pool_chunk_size and innodb_buffer_pool_instances will be set accordingly?
    – datascinalyst
    Commented Nov 24, 2023 at 5:20
  • Yes, for that size, there will be 8 pool instances and the chunk size will be 128MB. So each pool instance will be 4G, and there will be 32 chunks in each instance. You don't have to specify that in the configuration, it's handled by default.
    – Bill Karwin
    Commented Nov 24, 2023 at 5:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.