MYSQL 5.7 Innodb bufferpool settings

MySQLmysql-5.7

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?

Best Answer

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

Related Question