Bug #72184 InnoDB buffer pool status variables (Innodb_buffer_pool_pages*)

Submitted: 1 Apr 2014 12:58 Modified: 2 Apr 2014 17:55
Reporter: Ceri Williams Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.5, 5.6 OS:Linux
Assigned to: CPU Architecture:Any

[1 Apr 2014 12:58] Ceri Williams
Description:
According to the manual:

Innodb_buffer_pool_pages_data:  The number of pages in the InnoDB buffer pool containing data. The number includes both dirty and clean pages.
Innodb_buffer_pool_pages_free:  The number of free pages in the InnoDB buffer pool.
Innodb_buffer_pool_pages_total: The total size of the InnoDB buffer pool, in pages.

So, the following should be true:

Innodb_buffer_pool_pages_data + Innodb_buffer_pool_pages_free = Innodb_buffer_pool_pages_total

However, this does not appear to be the case, which means either the documentation is unclear or the variables do not represent the data that they should, e.g.

Innodb_buffer_pool_pages_data  = 7254112
Innodb_buffer_pool_pages_free  = 34816
Innodb_buffer_pool_pages_total = 4456414

7254112 + 34816   = 7288928
7288928 / 4456414 = 1.63

How to repeat:
bp_data=$(mysql -Nsq -e"show global status like 'Innodb_buffer_pool_pages_data'"|tr '\t' , )
bp_total=$(mysql -Nsq -e"show global status like 'Innodb_buffer_pool_pages_total'"|tr '\t' , )
bp_free=$(mysql -Nsq -e"show global status like 'Innodb_buffer_pool_pages_free'"|tr '\t' , )

echo ${bp_data}
echo ${bp_total}
echo ${bp_free}

echo "scale=2; ( $(echo ${bp_data} | cut -f2 -d,) + $(echo ${bp_free} | cut -f2 -d,) ) / $(echo ${bp_total} | cut -f2 -d,)" | bc

Suggested fix:
Either fix the data for the variables, or clarify the documentation.
[1 Apr 2014 15:45] Valeriy Kravchuk
What is the value of Innodb_buffer_pool_pages_misc and does it explain the difference?
[2 Apr 2014 17:54] Arnaud Adant
Valeriy was right to ask for innodb_buffer_pool_pages_misc.

This bug is probably a duplicate of this one :

http://bugs.mysql.com/bug.php?id=59550
[2 Apr 2014 17:55] Arnaud Adant
It occurs when using compressed pages from 5.1 + InnoDB plugin.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.053 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.