InnoDB INFORMATION_SCHEMA缓冲池表
该InnoDB
INFORMATION_SCHEMA
缓冲池表提供缓冲关于内页池状态信息和元数据InnoDB
缓冲池。
该InnoDB
INFORMATION_SCHEMA
缓冲池表包括那些列举如下:
mysql>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列表中的页面顺序,LRU列表确定在缓冲池已满时应从缓冲池中退出哪些页面。该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
orINNODB_BUFFER_PAGE_LRU
表可能会影响性能。除非您了解性能影响并确定可接受,否则不要在生产系统上查询这些表。为避免影响生产系统的性能,请重现要调查的问题并在测试实例上查询缓冲池统计信息。
示例15.6在INNODB_BUFFER_PAGE表中查询系统数据
该查询通过排除表名称中TABLE_NAME
值为NULL
或包含斜杠/
或句点.
(指示用户定义的表)的页面来提供包含系统数据的页面的近似计数。
mysql>SELECT COUNT(*)FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGEWHERE TABLE_NAME IS NULL OR (INSTR(TABLE_NAME , '/') = 0 AND INSTR(TABLE_NAME , '.') = 0); +---------- + | COUNT(*) | +---------- + | 1516 | +---------- +
该查询返回包含系统数据的页面的大约数量,缓冲池页面的总数以及包含系统数据的页面的大约百分比。
mysql>SELECT (SELECT COUNT(*)FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGEWHERE 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
在包含系统数据的页面中返回八个不同的值:
mysql>SELECT DISTINCT PAGE_TYPEFROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGEWHERE 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 | +------------------- +
示例15.7在INNODB_BUFFER_PAGE表中查询用户数据
该查询通过对TABLE_NAME
值为NOT NULL
和的页面进行计数来提供包含用户数据的页面的近似计数NOT LIKE '%INNODB_TABLES%'
。
mysql>SELECT COUNT(*)FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGEWHERE TABLE_NAME IS NOT NULL ANDTABLE_NAME NOT LIKE '%INNODB_TABLES%'; +---------- + | COUNT(*) | +---------- + | 7897 | +---------- +
该查询返回包含用户数据的页面的大约数量,缓冲池页面的总数以及包含用户数据的页面的大约百分比。
mysql>SELECT (SELECT COUNT(*)FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGEWHERE 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 | +------------ +------------- +---------------------- +
此查询使用缓冲池中的页面标识用户定义的表:
mysql>SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGEWHERE TABLE_NAME IS NOT NULL AND (INSTR(TABLE_NAME , '/') > 0 OR INSTR(TABLE_NAME , '.') > 0) ANDTABLE_NAME NOT LIKE '`mysql`.`innodb_%'; +------------------------- + | TABLE_NAME | +------------------------- + | `employees`.`salaries` | | `employees`.`employees` | +------------------------- +
示例15.8在INNODB_BUFFER_PAGE表中查询索引数据
有关索引页的信息,请INDEX_NAME
使用索引名称查询列。例如,以下查询返回表emp_no
上定义的索引的页面数和页面的总数据大小employees.salaries
:
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_PAGEWHERE INDEX_NAME='emp_no' ANDTABLE_NAME = '`employees`.`salaries`'; +------------ +------- +----------------- + | INDEX_NAME | Pages | Total Data (MB) | +------------ +------- +----------------- + | emp_no | 1609 | 25 | +------------ +------- +----------------- +
该查询返回employees.salaries
表上定义的所有索引的页面数和页面的总数据大小:
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_PAGEWHERE TABLE_NAME = '`employees`.`salaries`'GROUP BY INDEX_NAME; +------------ +------- +----------------- + | INDEX_NAME | Pages | Total Data (MB) | +------------ +------- +----------------- + | emp_no | 1608 | 25 | | PRIMARY | 6086 | 95 | +------------ +------- +----------------- +
示例15.9在INNODB_BUFFER_PAGE_LRU表中查询LRU_POSITION数据
该INNODB_BUFFER_PAGE_LRU
表保存有关InnoDB
缓冲池中页面的信息,尤其是它们的排序方式,以确定在缓冲池已满时要从缓冲池中退出哪些页面。该页面的定义与的定义相同INNODB_BUFFER_PAGE
,不同之处在于此表具有一LRU_POSITION
列而不是一BLOCK_ID
列。
此查询计算LRU列表中特定位置在employees.employees
表页面所占据的位置数量。
mysql>SELECT COUNT(LRU_POSITION)FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE_LRUWHERE TABLE_NAME ='`employees`.`employees`' AND LRU_POSITION < 3072; +--------------------- + | COUNT(LRU_POSITION) | +--------------------- + | 548 | +--------------------- +
示例15.10查询INNODB_BUFFER_POOL_STATS表
该INNODB_BUFFER_POOL_STATS
表提供了SHOW ENGINE INNODB STATUS
与InnoDB
缓冲池状态变量相似的信息。
mysql>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
输出的更多信息,请参见“ InnoDB标准监视器和锁定监视器输出”。
mysql>SHOW ENGINE INNODBSTATUS \G ... ---------------------- BUFFER POOL ANDMEMORY ---------------------- Total largememory allocated 137428992 Dictionarymemory allocated 579084 Buffer pool size 8192 Free buffers 1Database pages 8173Old database pages 3014 Modified db pages 0 Pendingreads 0 Pending writes: LRU 0,flush list 0, singlepage 0 Pages made young 15907, not young 3803101 0.00 youngs/s, 0.00 non-youngs/s Pagesread 3270, created 13176, written 15109 0.00reads /s, 0.00 creates/s, 0.00 writes/sNo buffer poolpage gets since thelast printout Pagesread ahead 0.00/s, evictedwithout 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] ...
有关状态变量的说明,请参见“服务器状态变量”。
mysql>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 | +--------------------------------------- +------------- +