content_image/703093.jpg
## Table Overview 

• INNODB_BUFFER_PAGE : Holds information about each page in the InnoDB  buffer pool. 

• INNODB_BUFFER_PAGE_LRU : Holds information about the pages in the InnoDB  buffer pool, in particular how they are ordered in the LRU list that determines which pages to evict from the buffer pool when it becomes full. The INNODB_BUFFER_PAGE_LRU  table has the same columns as the INNODB_BUFFER_PAGE  table, except that the INNODB_BUFFER_PAGE_LRU  table has an LRU_POSITION  column instead of a BLOCK_ID  column. 

• INNODB_BUFFER_POOL_STATS : Provides buffer pool status information. Much of the same information is provided by SHOW ENGINE INNODB STATUS  output, or may be obtained using InnoDB  buffer pool server status variables. 

<img src='content_image/703093.jpg'>

## Example 15.6 Querying System Data in the INNODB_BUFFER_PAGE Table 

## Warning 

Querying the INNODB_BUFFER_PAGE  or INNODB_BUFFER_PAGE_LRU  table 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. 

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. 

mysql> SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE WHERE TABLE_NAME IS NULL OR (INSTR(TABLE_NAME, ’/’) = 0 AND INSTR(TABLE_NAME, ’.’) = 0); +----------+ | COUNT(*) | +----------+ | 1516 | +----------+ 

This query returns the approximate number of pages that contain system data, the total number of buffer pool pages, and an approximate percentage of pages that contain system data. 

mysql> SELECT (SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE WHERE TABLE_NAME IS NULL OR (INSTR(TABLE_NAME, ’/’) = 0 AND INSTR(TABLE_NAME, ’.’) = 0) ) AS system_pages, ( SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE ) AS total_pages, ( SELECT ROUND((system_pages/total_pages) * 100) ) AS system_page_percentage; +--------------+-------------+------------------------+ | system_pages | total_pages | system_page_percentage | +--------------+-------------+------------------------+ | 295 | 8192 | 4 | +--------------+-------------+------------------------+ 

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: 

mysql> SELECT DISTINCT PAGE_TYPE FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE WHERE TABLE_NAME IS NULL OR (INSTR(TABLE_NAME, ’/’) = 0 AND INSTR(TABLE_NAME, ’.’) = 0); 

