1

I have set innodb_buffer_pool_size=100G and instances to 8, but I am getting following output

mysql> show engine innodb status \G
---BUFFER POOL 6
Buffer pool size   835482

I have been reading online that bufferpool= innodb_buffer_pool_chunk_size* innodb_buffer_pool_instances.

This is confusing, should we set the default chunk size to higher values and increasing the number of instances do not cause contention anymore.

Can anyone advise on correct parameters for 100GB bufferpool size?

0

There is no problem, just confusing notation. It is saying that there are 835482 blocks in instance #6.

Here is a buffer_pool of 3G and 8 instances:

mysql> SHOW VARIABLES LIKE 'innodb_buffer_pool%';
...
| innodb_buffer_pool_instances        | 8              |
| innodb_buffer_pool_size             | 3145728000     |  -- 3GB

mysql> SHOW ENGINE INNODB STATUS\G
*************************** 1. row ***************************
...
---BUFFER POOL 7
Buffer pool size        23999       -- 3G / 8 / 16K
Buffer pool size, bytes 393199616   -- 3G / 8

In your case, 835482 = 100G / 8 / 16K

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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