| 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 | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Documentation | Severity: | S3 (Non-critical) |
| Version: | 5.7.10 | OS: | CentOS (7) |
| Assigned to: | CPU Architecture: | Any | |
[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]
MySQL Verification Team
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.
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.