MySQL Bugs: #79843: Buffer pool size differs if we calculate it from engine status output

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)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	5.6.27, 5.6.28, 5.7.10	OS:	Any
Assigned to:		CPU Architecture:	Any

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.

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.

Updated Category to InnoDB

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

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

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)