+-------------------+ | PAGE_TYPE | +-------------------+ | SYSTEM | | IBUF_BITMAP | | UNKNOWN | | FILE_SPACE_HEADER | | INODE | | UNDO_LOG | | ALLOCATED | +-------------------+ 

## Example 15.7 Querying User Data in the INNODB_BUFFER_PAGE Table 

This query provides an approximate count of pages containing user data by counting pages where the TABLE_NAME  value is NOT NULL  and NOT LIKE ’%INNODB_TABLES%’ . 

mysql> SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE WHERE TABLE_NAME IS NOT NULL AND TABLE_NAME NOT LIKE ’%INNODB_TABLES%’; +----------+ | COUNT(*) | +----------+ | 7897 | +----------+ 

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

mysql> SELECT (SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE WHERE TABLE_NAME IS NOT NULL AND (INSTR(TABLE_NAME, ’/’) > 0 OR INSTR(TABLE_NAME, ’.’) > 0) ) AS user_pages, ( SELECT COUNT(*) FROM information_schema.INNODB_BUFFER_PAGE ) AS total_pages, ( SELECT ROUND((user_pages/total_pages) * 100) ) AS user_page_percentage; +------------+-------------+----------------------+ | user_pages | total_pages | user_page_percentage | +------------+-------------+----------------------+ | 7897 | 8192 | 96 | +------------+-------------+----------------------+ 

This query identifies user-defined tables with pages in the buffer pool: 

mysql> SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE WHERE TABLE_NAME IS NOT NULL AND (INSTR(TABLE_NAME, ’/’) > 0 OR INSTR(TABLE_NAME, ’.’) > 0) AND TABLE_NAME NOT LIKE ’‘mysql‘.‘innodb_%’; +-------------------------+ | TABLE_NAME | +-------------------------+ | ‘employees‘.‘salaries‘ | | ‘employees‘.‘employees‘ | +-------------------------+ 

## Example 15.8 Querying Index Data in the INNODB_BUFFER_PAGE Table 

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: 

mysql> SELECT INDEX_NAME, COUNT(*) AS Pages, ROUND(SUM(IF(COMPRESSED_SIZE = 0, @@GLOBAL.innodb_page_size, COMPRESSED_SIZE))/1024/1024) AS ’Total Data (MB)’ FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE 

