Querying the INNODB_BUFFER_PAGE or
INNODB_BUFFER_PAGE_LRU table can
can affect performance. Do not query these tables on a
production system unless you are aware of the performance impact
and have determined it to be acceptable. To avoid impacting
performance on a production system, reproduce the issue you want
to investigate and query buffer pool statistics on a test
instance.

Example 14.2 Querying System Data in the INNODB_BUFFER_PAGE Table

This query provides an approximate count of pages that contain
system data by excluding pages where the
TABLE_NAME value is either
NULL or includes a slash /
or period . in the table name, which
indicates a user-defined table.

The type of system data in the buffer pool can be determined by
querying the PAGE_TYPE value. For example,
the following query returns eight distinct
PAGE_TYPE values among the pages that contain
system data:

For information about index pages, query the
INDEX_NAME column using the name of the
index. For example, the following query returns the number of
pages and total data size of pages for the
emp_no index that is defined on the
employees.salaries table:

The INNODB_BUFFER_PAGE_LRU table
holds information about the pages in the
InnoDB buffer pool, in particular how they
are ordered that determines which pages to evict from the buffer
pool when it becomes full. The definition for this page is the
same as for INNODB_BUFFER_PAGE,
except this table has an LRU_POSITION column
instead of a BLOCK_ID column.

This query counts the number of positions at a specific location
in the LRU list occupied by pages of the
employees.employees table.