Bug #79843 Buffer pool size differs if we calculate it from engine status output

• Submitted: 5 Jan 2016 8:38
• Modified: 12 Feb 2016 8:00
• Reporter: Shahriyar Rzayev (OCA)
• Status: Verified
• Category: MySQL Server: InnoDB storage engine
• Severity: S2 (Serious)
• Version: 5.6.27, 5.6.28, 5.7.10
• OS: Any
• CPU Architecture: Any

[5 Jan 2016 8:38] Shahriyar Rzayev

Description:
To obtain buffer pool size information we can use output from:
SHOW engine innodb status:

---BUFFER POOL 63
Buffer pool size   296959

as Documentation states:

"The total size you specify is divided among all the buffer pools":
https://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-buffer-pools.html

So calculation will be something like:

mysql > select (select @@innodb_page_size)*296959*64;
+---------------------------------------+
| (select @@innodb_page_size)*296959*64 |
+---------------------------------------+
|                          311384080384 |
+---------------------------------------+
1 row in set (0,00 sec)

2nd way just querying for variable:

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

Result:

311384080384 is not equal to 311385128960

How to repeat:
See description

Suggested fix:
If it is not a bug please update documentation to point this out.

[5 Jan 2016 12:14] Shahriyar Rzayev

The difference is :

311385128960 - 311384080384 = 1048576 bytes (1024 KB = 1MB)

1048576 / 64 = 16384 bytes -> 1 page

So we have missing 1 page for each buffer pool instance.

[5 Jan 2016 12:17] Shahriyar Rzayev

Updated Category to InnoDB

[7 Jan 2016 7:35] MySQL Verification Team

Too add to this discussion,  I had filed this a while ago to just get rid of the individual buffer pool stats:

https://bugs.mysql.com/bug.php?id=77431
(why spam INDIVIDUAL BUFFER POOL INFO sections in show engine innodb status?)

[14 Jan 2016 8:57] MySQL Verification Team

Hello Shahriyar,

Thank you for the report.
Observed the issue with 5.6.28 build.
Imho this is more or less sounds like a doc request than bug.

Thanks,
Umesh

[12 Feb 2016 8:00] Shahriyar Rzayev

Also the same thing with using INFORMATION_SCHEMA.INNODB_BUFFER_POOL_STATS table:

mysql> select pool_id, pool_size from INFORMATION_SCHEMA.INNODB_BUFFER_POOL_STATS limit 1;
+---------+-----------+
| pool_id | pool_size |
+---------+-----------+
|       0 |     16382 |
+---------+-----------+
1 row in set (0,00 sec)

mysql> select (select @@innodb_page_size)*16382*64;
+--------------------------------------+
| (select @@innodb_page_size)*16382*64 |
+--------------------------------------+
|                          17177772032 |
+--------------------------------------+
1 row in set (0,00 sec)

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