How to get confused about buffer pool size allocation?

How it feels when you realize that, something going to be scary in term of question – “Wait, why? how?”
Let’s see:

I am trying to allocate 9G buffer pool size with 64 instances:

innodb_buffer_pool_instances=64
innodb_buffer_pool_size=9G

From error log it said that:

InnoDB: Initializing buffer pool, total size = 16G, instances = 64, chunk size = 128M

“Wait, why? how?” N1 🙂

From select it is also allocated 16G:

mysql> select @@innodb_buffer_pool_size/1024/1024/1024;
+------------------------------------------+
| @@innodb_buffer_pool_size/1024/1024/1024 |
+------------------------------------------+
| 16.000000000000 |
+------------------------------------------+

Just continue to increase pool size:

If you give 17G:

[Note] InnoDB: Initializing buffer pool, total size = 24G, instances = 64, chunk size = 128M

If you give 25G:

[Note] InnoDB: Initializing buffer pool, total size = 32G, instances = 64, chunk size = 128M

If you give 33G:

[Note] InnoDB: Initializing buffer pool, total size = 40G, instances = 64, chunk size = 128M

So each time it will allocate 7G more

“Wait, why? how?” N2 🙂

But the situation is getting more fun when you read the doc:

http://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_buffer_pool_chunk_size

Buffer pool size must always be a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances. If you alter innodb_buffer_pool_chunk_size, innodb_buffer_pool_size is automatically adjusted to a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances that is not less than current buffer pool size. The adjustment occurs when the buffer pool is initialized.

So in fact Buffer Pool Size is always multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances?
Then it must be 128M * 64 = 8G.

It doesn’t matter that, you give 33G in my.cnf and then you learn from Log it is allocated size is 40G, then you learn that it is in fact 8G in size 🙂

Keep calm there is already a related BUG report -> http://bugs.mysql.com/bug.php?id=80350

Special thanks to Zhai Weixiang for comment in bug report.

1 thought on “How to get confused about buffer pool size allocation?”

Leave a Reply

Your email address will not be published. Required fields are marked *