21.29.17 The INFORMATION_SCHEMA INNODB_BUFFER_PAGE_LRU Table

The INNODB_BUFFER_PAGE_LRU table holds information about the pages in the InnoDB buffer pool, in particular how they are ordered in the LRU list that determines which pages to evict from the buffer pool when it becomes full.

The definition for this page is the same as for INNODB_BUFFER_PAGE, except this table has an LRU_POSITION column instead of BLOCK_ID.

Warning

Querying the INNODB_BUFFER_PAGE_LRU table can introduce significant performance overhead. Do not query this table on a production system unless you are aware of the performance impact that your query may have and have determined it to be acceptable. To avoid impacting performance, reproduce the issue you want to investigate on a test instance and query the INNODB_BUFFER_PAGE_LRU table on the test instance.

Table 21.19 INNODB_BUFFER_PAGE_LRU Columns

Column nameDescription
POOL_IDBuffer Pool ID. An identifier to distinguish between multiple buffer pool instances.
LRU_POSITIONThe position of the page in the LRU list.
SPACETablespace ID. Uses the same value as in INNODB_SYS_TABLES.SPACE.
PAGE_NUMBERPage number.
PAGE_TYPEPage type. One of 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).
FLUSH_TYPEFlush type.
FIX_COUNTNumber of threads using this block within the buffer pool. When zero, the block is eligible to be evicted.
IS_HASHEDWhether hash index has been built on this page.
NEWEST_MODIFICATIONLog Sequence Number of the youngest modification.
OLDEST_MODIFICATIONLog Sequence Number of the oldest modification.
ACCESS_TIMEAn abstract number used to judge the first access time of the page.
TABLE_NAMEName of the table the page belongs to. This column is only applicable to pages of type INDEX.
INDEX_NAMEName 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_RECORDSNumber of records within the page.
DATA_SIZESum of the sizes of the records. This column is only applicable to pages of type INDEX.
COMPRESSED_SIZECompressed page size. Null for pages that are not compressed.
PAGE_STATEPage 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_USE, NOT_USED, REMOVE_HASH.
IO_FIXSpecifies whether any I/O is pending for this page: IO_NONE = no pending I/O, IO_READ = read pending, IO_WRITE = write pending.
IS_OLDSpecifies whether or not the block is in the sublist of old blocks in the LRU list.
FREE_PAGE_CLOCKThe value of the freed_page_clock counter when the block was the last placed at the head of the LRU list. The freed_page_clock counter 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: 0
1 row in set (0.02 sec)

Notes