2,158 total views, 2 views today

INNODB_BUFFER_PAGE_LRU 表存在于INFORMATION_SCHEMA中,它记录了InnoDB buffer pool中所有pages的信息,特别是当buffer pool满了之后,LRU列表决定了按照顺序驱逐pages。



Column name Description
POOL_ID Buffer Pool ID. An identifier to distinguish between multiple buffer pool instances.
多个buffer pool时,page所在buffer pool 的id。
LRU_POSITION The position of the page in the LRU list.
SPACE Tablespace ID. Uses the same value as in INNODB_SYS_TABLES.SPACE.
MySQL InnoDB buffer pool预热机制保存数据时,需要的tablespace id就是获取的这列。
PAGE_NUMBER Page number.
PAGE_TYPE Page type. Permitted values are ALLOCATED (Freshly allocated page), INDEX (B-tree node), UNDO_LOG (Undo log page), INODE(Index node), IBUF_FREE_LIST (Insert buffer free list), IBUF_BITMAP (Insert buffer bitmap), SYSTEM (System page), TRX_SYSTEM(Transaction system data), FILE_SPACE_HEADER (File space header), EXTENT_DESCRIPTOR (Extent descriptor page), BLOB(Uncompressed BLOB page), COMPRESSED_BLOB (First compressed BLOB page), COMPRESSED_BLOB2 (Subsequent comp BLOB page), IBUF_INDEX (Insert buffer index), UNKNOWN (unknown).
Page的类型,如INDEX、UNDO_LOG、system page、TRX_SYSTEM等类型。
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.
thread从buffer pool获取的block数量。
IS_HASHED Whether hash index has been built on this page.
NEWEST_MODIFICATION Log Sequence Number of the youngest modification.
OLDEST_MODIFICATION Log Sequence Number of the oldest modification.
ACCESS_TIME An abstract number used to judge the first access time of the page.
TABLE_NAME Name of the table the page belongs to. This column is only applicable to pages of type INDEX.
INDEX_NAME Name of the index the page belongs to. It can be the name of a clustered index or a secondary index. This column is only applicable to pages of type INDEX.
NUMBER_RECORDS Number of records within the page.
DATA_SIZE Sum of the sizes of the records. This column is only applicable to pages of type INDEX.
COMPRESSED_SIZE Compressed page size. Null for pages that are not compressed.
PAGE_STATE Page state. A page with valid data has one of the following states: FILE_PAGE (buffers a page of data from a file), MEMORY(buffers a page from an in-memory object), COMPRESSED. Other possible states (managed by InnoDB) are: NULL,READY_FOR_USENOT_USEDREMOVE_HASH.
IO_FIX Specifies whether any I/O is pending for this page: IO_NONE = no pending I/O, IO_READ = read pending, IO_WRITE = write pending.
IS_OLD Specifies whether or not the block is in the sublist of old blocks in the LRU list.
FREE_PAGE_CLOCK The value of the freed_page_clock counter when the block was the last placed at the head of the LRU list. Thefreed_page_clock counter tracks the number of blocks removed from the end of the LRU list.




  • 查询该表必须有PROCESS权限;
  • 使用DESC 、SHOW COLUMNS命令去查看列和数据类型的信息;
  • 查询INNODB_BUFFER_PAGE_LRU表需要分配连续的内存,特别是InnoDB buffer pool上G容量的时候,可能会导致OOM(out-of-memory);
  • 查询INNODB_BUFFER_PAGE_LRU表会锁定LRU列中的数据结构,特别是InnoDB buffer pool上G容量的时候,会导致并发性下降。
  • 删除表、表数据、分区或者索引,这些被删除的对象的page并不会马上从buffer pool中清除,直到空间紧张,才把它们删除。