MySQL 8.0 Release Notes
MySQL 8.0 Source Code Documentation
这INNODB_BUFFER_PAGE_LRUtable provides information about the pages in theInnoDBbuffer pool; in particular, how they are ordered in the LRU list that determines which pages toevictfrom the buffer pool when it becomes full.
这INNODB_BUFFER_PAGE_LRUtable has the same columns as theINNODB_BUFFER_PAGEtable with a few exceptions. It hasLRU_POSITIONandCOMPRESSEDcolumns instead ofBLOCK_IDandPAGE_STATEcolumns, and it does not include andIS_STALEcolumn.
For related usage information and examples, see第15.15.5节“InnoDB Information_Schema缓冲池表”。
Warning
Querying theINNODB_BUFFER_PAGE_LRUtable can affect performance. Do not query this table 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.
这INNODB_BUFFER_PAGE_LRUtable has these columns:
pool_id.这buffer pool ID. This is an identifier to distinguish between multiple buffer pool instances.
LRU_POSITION这position of the page in the LRU list.
SPACE表空间ID;与之相同的价值
INNODB_TABLES.SPACE。PAGE_NUMBER页码。
PAGE_TYPE这page type. The following table shows the permitted values.
表26.6 Innodb_buffer_page_lru.page_type值
Page Type 描述 分配Freshly allocated page 斑点未压缩的斑点页面 COMPRESSED_BLOB2Subsequent comp BLOB page COMPRESSED_BLOBFirst compressed BLOB page ENCRYPTED_RTREEEncrypted R-tree steate_descriptorExtent descriptor page FILE_SPACE_HEADERFile space header fil_page_type_unused.Unused IBUF_BITMAPInsert buffer bitmap IBUF_FREE_LISTInsert buffer free list IBUF_INDEX插入缓冲索引 INDEXB树节点 INODE索引节点 lob_data.Uncompressed LOB data LOB_FIRSTFirst page of uncompressed LOB lob_index.未压缩的LOB索引 PAGE_IO_COMPRESSEDCompressed page page_io_compression_encryptedCompressed and encrypted page PAGE_IO_ENCRYPTEDEncrypted page RSEG_ARRAYRollback segment array RTREE_INDEXR-tree index SDI_BLOBUncompressed SDI BLOB sdi_compression_blob.Compressed SDI BLOB SDI_INDEXSDI index SYSTEMSystem page trx_system.Transaction system data UNDO_LOG撤消日志页面 UNKNOWNUnknown ZLOB_DATACompressed LOB data ZLOB_FIRSTFirst page of compressed LOB ZLOB_FRAGCompressed LOB fragment zlob_frag_entry.压缩的LOB片段指数 ZLOB_INDEX压缩LOB指数 FLUSH_TYPE这flush type.
fix_count.这number of threads using this block within the buffer pool. When zero, the block is eligible to be evicted.
is_hashed.是否在此页面上建立了哈希索引。
newest_modification.日志序列号的最年轻修改。
OLDEST_MODIFICATION这Log Sequence Number of the oldest modification.
ACCESS_TIMEAn abstract number used to judge the first access time of the page.
TABLE_NAME页面所属的表的名称。此列仅适用于带有的页面
PAGE_TYPEvalue ofINDEX。列是NULLif the server has not yet accessed the table.INDEX_NAME这name of the index the page belongs to. This can be the name of a clustered index or a secondary index. This column is applicable only to pages with a
PAGE_TYPEvalue ofINDEX。number_records.这number of records within the page.
DATA_SIZE这sum of the sizes of the records. This column is applicable only to pages with a
PAGE_TYPEvalue ofINDEX。COMPRESSED_SIZE这compressed page size.
NULLfor pages that are not compressed.COMPRESSEDWhether the page is compressed.
IO_FIXWhether any I/O is pending for this page:
IO_NONE.= no pending I/O,IO_READ= read pending,IO_WRITE= write pending.IS_OLDWhether the block is in the sublist of old blocks in the LRU list.
FREE_PAGE_CLOCK这value of the
freed_page_clock车辆时计数器ck was the last placed at the head of the LRU list. Thefreed_page_clockcounter tracks the number of blocks removed from the end of the LRU list.
Example
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE_LRU LIMIT 1\G *************************** 1. row *************************** POOL_ID: 0 LRU_POSITION: 0 SPACE: 97 PAGE_NUMBER: 1984 PAGE_TYPE: INDEX FLUSH_TYPE: 1 FIX_COUNT: 0 IS_HASHED: YES NEWEST_MODIFICATION: 719490396 OLDEST_MODIFICATION: 0 ACCESS_TIME: 3378383796 TABLE_NAME: `employees`.`salaries` INDEX_NAME: PRIMARY NUMBER_RECORDS: 468 DATA_SIZE: 14976 COMPRESSED_SIZE: 0 COMPRESSED: NO IO_FIX: IO_NONE IS_OLD: YES FREE_PAGE_CLOCK: 0Notes
This table is useful primarily for expert-level performance monitoring, or when developing performance-related extensions for MySQL.
You must have the
PROCESSprivilege to query this table.Use the
Information_Schema.COLUMNStable or theSHOW COLUMNSstatement to view additional information about the columns of this table, including data types and default values.Querying this table can require MySQL to allocate a large block of contiguous memory, more than 64 bytes times the number of active pages in the buffer pool. This allocation could potentially cause an out-of-memory error, especially for systems with multi-gigabyte buffer pools.
查询此表需要MySQL锁定表示缓冲池的数据结构，同时遍历LRU列表，可以减少并发性，尤其是具有多千兆字节缓冲池的系统。
删除表，表行，分区或索引时，关联的页面保留在缓冲池中，直到其他数据需要空间。这
INNODB_BUFFER_PAGE_LRUtable reports information about these pages until they are evicted from the buffer pool. For more information about how theInnoDB缓冲池管理数据,看看第15.5.1节“缓冲池”。