Bug #80350 Innodb allocates 7G more buffer pool size from given in my.cnf
Submitted: 12 Feb 2016 8:20 Modified: 16 Mar 2016 14:54
Reporter: Shahriyar Rzayev (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.7.10 OS:CentOS (7)
Assigned to: CPU Architecture:Any

[12 Feb 2016 8:20] Shahriyar Rzayev
Description:
Hi dear experts,

Trying to allocate 9G buffer pool size:

innodb_buffer_pool_instances=64
innodb_buffer_pool_size=9G

From log:

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

From query:

mysql> select @@innodb_buffer_pool_size/1024/1024/1024;
+------------------------------------------+
| @@innodb_buffer_pool_size/1024/1024/1024 |
+------------------------------------------+
|                          16.000000000000 |
+------------------------------------------+
1 row in set (0,00 sec)

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 why?

OS info:

[root@mysql-57 ~]# uname -a
Linux mysql-57 3.10.0-327.4.4.el7.x86_64 #1 SMP Tue Jan 5 16:07:00 UTC 2016 x86_64 x86_64 x86_64 GNU/Linux

[root@mysql-57 ~]# free -m
              total        used        free      shared  buff/cache   available
Mem:           3428        2667         645           0         115         635
Swap:          2047        1208         839

How to repeat:
See description

Suggested fix:
If it is an intended behaviour, please provide related info.
[12 Feb 2016 8:55] zhai weixiang
Check function buf_pool_size_align , it seems the real buffer pool size will be aligned with instances * chunk_unit...(which is 8GB in your test case)

It's a documented behavior: 
http://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_buffer_pool_ch...

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.
[12 Feb 2016 10:36] Shahriyar Rzayev
Thanks for reply.
I am a bit confused in related topic because of followings:

http://dev.mysql.com/doc/refman/5.7/en/innodb-buffer-pool-online-resize.html

"When you increase or decrease innodb_buffer_pool_size online, the operation is performed in chunks" -> it is about ONLINE resizing yes? but I am changing buffer pool size using config file as was in old days :)

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

"The following conditions apply when altering the innodb_buffer_pool_chunk_size value:

If innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances is larger than the current buffer pool size when the buffer pool is initialized, innodb_buffer_pool_chunk_size is truncated to innodb_buffer_pool_size / innodb_buffer_pool_instances.

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."

when altering the innodb_buffer_pool_chunk_size? Again I am not altering anything.
[12 Feb 2016 11:27] Shahriyar Rzayev
So even same logic works for offline buffer pool resizing. The situation is quite  messy for me:

1. I have requested 9G buff size(my.cnf)
2. It is in fact allocated 8G (See Zhai's comment)
3. From error log it is allocated 16G
4. From @@innodb_buffer_pool_size = 16G
5. From information_schema.INNODB_BUFFER_POOL_STATS = 16G
6. From engine status = 16G
[17 Feb 2016 16:00] Sinisa Milivojevic
Many thanks to Zhai  Weixiang who has cleared up the muddy waters for the bug reporter.

It is supposed that user of MySQL database knows what he / she is doing when setting certain parameters. However, I did notice that this matter is not handled properly in our user manual. Hence, this is a verified documentation bug.
[16 Mar 2016 14:54] Daniel Price
Posted by developer:
 
The following sections have been revised:

https://dev.mysql.com/doc/refman/5.7/en/innodb-buffer-pool-resize.html
https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_buffer_pool_s...

Thank you for the bug report.