该 InnoDB
INFORMATION_SCHEMA
缓冲池表提供缓冲池状态信息和 InnoDB
缓冲池中的页面元数据。
该 InnoDB
INFORMATION_SCHEMA
缓冲池表包括以下表:
Press CTRL+C to copymysql> SHOW TABLES FROM INFORMATION_SCHEMA LIKE 'INNODB_BUFFER%'; +-----------------------------------------------+ | Tables_in_INFORMATION_SCHEMA (INNODB_BUFFER%) | +-----------------------------------------------+ | INNODB_BUFFER_PAGE_LRU | | INNODB_BUFFER_PAGE | | INNODB_BUFFER_POOL_STATS | +-----------------------------------------------+
表概述
-
INNODB_BUFFER_PAGE
:包含InnoDB
缓冲池中每个页面的信息。 -
INNODB_BUFFER_PAGE_LRU
:包含InnoDB
缓冲池中页面的信息,特别是它们在 LRU 列表中的顺序,该列表确定了从缓冲池中 evict 哪些页面以满足缓冲池的需求。INNODB_BUFFER_PAGE_LRU
表具有与INNODB_BUFFER_PAGE
表相同的列,除了INNODB_BUFFER_PAGE_LRU
表具有LRU_POSITION
列,而不是BLOCK_ID
列。 -
INNODB_BUFFER_POOL_STATS
:提供缓冲池状态信息。许多相同的信息可以通过SHOW ENGINE INNODB STATUS
输出获得,或者可以使用InnoDB
缓冲池服务器状态变量获得。
查询 INNODB_BUFFER_PAGE
或 INNODB_BUFFER_PAGE_LRU
表可能会影响性能。在生产系统上不要查询这些表,除非您了解性能影响并确定其可接受。要避免在生产系统上影响性能,请在测试实例上重现要调查的问题并查询缓冲池统计信息。
示例 17.6 在 INNODB_BUFFER_PAGE 表中查询系统数据
该查询提供了系统数据页面的近似计数,方法是排除表名为 NULL
或包含斜杠 /
或点 .
的页面,这些页面表示用户定义的表。
Press CTRL+C to copymysql> 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 | +----------+
该查询返回系统数据页面的近似数量、总缓冲池页面数量和系统数据页面的近似百分比。
Press CTRL+C to copymysql> 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 | +--------------+-------------+------------------------+
可以通过查询 PAGE_TYPE
值来确定缓冲池中的系统数据类型。例如,以下查询返回八个不同的 PAGE_TYPE
值,这些值来自包含系统数据的页面:
Press CTRL+C to copymysql> 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); +-------------------+ | PAGE_TYPE | +-------------------+ | SYSTEM | | IBUF_BITMAP | | UNKNOWN | | FILE_SPACE_HEADER | | INODE | | UNDO_LOG | | ALLOCATED | +-------------------+
示例 17.7 在 INNODB_BUFFER_PAGE 表中查询用户数据
该查询提供了用户数据页面的近似计数,方法是计数表名不为 NULL
且不包含 '%INNODB_TABLES%'
的页面。
Press CTRL+C to copymysql> SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE WHERE TABLE_NAME IS NOT NULL AND TABLE_NAME NOT LIKE '%INNODB_TABLES%'; +----------+ | COUNT(*) | +----------+ | 7897 | +----------+
该查询返回用户数据页面的近似数量、总缓冲池页面数量和用户数据页面的近似百分比。
Press CTRL+C to copymysql> 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 | +------------+-------------+----------------------+
该查询标识了缓冲池中具有用户定义表的页面:
Press CTRL+C to copymysql> 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` | +-------------------------+
示例 17.8 在 INNODB_BUFFER_PAGE 表中查询索引数据
要获取索引页面的信息,可以使用索引名称查询 INDEX_NAME
列。例如,以下查询返回 emp_no
索引在 employees.salaries
表中的页面数量和总数据大小:
Press CTRL+C to copymysql> 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 WHERE INDEX_NAME='emp_no' AND TABLE_NAME = '`employees`.`salaries`'; +------------+-------+-----------------+ | INDEX_NAME | Pages | Total Data (MB) | +------------+-------+-----------------+ | emp_no | 1609 | 25 | +------------+-------+-----------------+
该查询返回 employees.salaries
表上的所有索引的页面数量和总数据大小:
Press CTRL+C to copymysql> 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 WHERE TABLE_NAME = '`employees`.`salaries`' GROUP BY INDEX_NAME; +------------+-------+-----------------+ | INDEX_NAME | Pages | Total Data (MB) | +------------+-------+-----------------+ | emp_no | 1608 | 25 | | PRIMARY | 6086 | 95 | +------------+-------+-----------------+
示例 17.9 在 INNODB_BUFFER_PAGE_LRU 表中查询 LRU_POSITION 数据
该 INNODB_BUFFER_PAGE_LRU
表保存了 InnoDB 缓冲池中页面的信息,特别是它们的顺序,这决定了在缓冲池满时从缓冲池中 evict 哪些页面。该表的定义与 INNODB_BUFFER_PAGE
相同,除了该表具有 LRU_POSITION
列,而不是 BLOCK_ID
列。
该查询统计了 employees.employees 表在 LRU 列表中特定位置所占用的页面数量。
Press CTRL+C to copymysql> SELECT COUNT(LRU_POSITION) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE_LRU WHERE TABLE_NAME='`employees`.`employees`' AND LRU_POSITION < 3072; +---------------------+ | COUNT(LRU_POSITION) | +---------------------+ | 548 | +---------------------+
示例 17.10 查询 INNODB_BUFFER_POOL_STATS 表
该 INNODB_BUFFER_POOL_STATS
表提供了类似于 SHOW ENGINE INNODB STATUS
和 InnoDB 缓冲池状态变量的信息。
Press CTRL+C to copymysql> SELECT * FROM information_schema.INNODB_BUFFER_POOL_STATS \G *************************** 1. row *************************** POOL_ID: 0 POOL_SIZE: 8192 FREE_BUFFERS: 1 DATABASE_PAGES: 8173 OLD_DATABASE_PAGES: 3014 MODIFIED_DATABASE_PAGES: 0 PENDING_DECOMPRESS: 0 PENDING_READS: 0 PENDING_FLUSH_LRU: 0 PENDING_FLUSH_LIST: 0 PAGES_MADE_YOUNG: 15907 PAGES_NOT_MADE_YOUNG: 3803101 PAGES_MADE_YOUNG_RATE: 0 PAGES_MADE_NOT_YOUNG_RATE: 0 NUMBER_PAGES_READ: 3270 NUMBER_PAGES_CREATED: 13176 NUMBER_PAGES_WRITTEN: 15109 PAGES_READ_RATE: 0 PAGES_CREATE_RATE: 0 PAGES_WRITTEN_RATE: 0 NUMBER_PAGES_GET: 33069332 HIT_RATE: 0 YOUNG_MAKE_PER_THOUSAND_GETS: 0 NOT_YOUNG_MAKE_PER_THOUSAND_GETS: 0 NUMBER_PAGES_READ_AHEAD: 2713 NUMBER_READ_AHEAD_EVICTED: 0 READ_AHEAD_RATE: 0 READ_AHEAD_EVICTED_RATE: 0 LRU_IO_TOTAL: 0 LRU_IO_CURRENT: 0 UNCOMPRESS_TOTAL: 0 UNCOMPRESS_CURRENT: 0
为了比较,SHOW ENGINE INNODB STATUS
输出和 InnoDB 缓冲池状态变量输出如下所示,基于同一数据集。
有关 SHOW ENGINE INNODB STATUS
输出的更多信息,请参阅 第 17.17.3 节,“InnoDB 标准监控和锁监控输出”。
Press CTRL+C to copymysql> SHOW ENGINE INNODB STATUS \G ... ---------------------- BUFFER POOL AND MEMORY ---------------------- Total large memory allocated 137428992 Dictionary memory allocated 579084 Buffer pool size 8192 Free buffers 1 Database pages 8173 Old database pages 3014 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 15907, not young 3803101 0.00 youngs/s, 0.00 non-youngs/s Pages read 3270, created 13176, written 15109 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 8173, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ...
有关状态变量描述,请参阅 第 7.1.10 节,“服务器状态变量”。
Press CTRL+C to copymysql> SHOW STATUS LIKE 'Innodb_buffer%'; +---------------------------------------+-------------+ | Variable_name | Value | +---------------------------------------+-------------+ | Innodb_buffer_pool_dump_status | not started | | Innodb_buffer_pool_load_status | not started | | Innodb_buffer_pool_resize_status | not started | | Innodb_buffer_pool_pages_data | 8173 | | Innodb_buffer_pool_bytes_data | 133906432 | | Innodb_buffer_pool_pages_dirty | 0 | | Innodb_buffer_pool_bytes_dirty | 0 | | Innodb_buffer_pool_pages_flushed | 15109 | | Innodb_buffer_pool_pages_free | 1 | | Innodb_buffer_pool_pages_misc | 18 | | Innodb_buffer_pool_pages_total | 8192 | | Innodb_buffer_pool_read_ahead_rnd | 0 | | Innodb_buffer_pool_read_ahead | 2713 | | Innodb_buffer_pool_read_ahead_evicted | 0 | | Innodb_buffer_pool_read_requests | 33069332 | | Innodb_buffer_pool_reads | 558 | | Innodb_buffer_pool_wait_free | 0 | | Innodb_buffer_pool_write_requests | 11985961 | +---------------------------------------+-------------+