5.3 The INFORMATION_SCHEMA INNODB_BUFFER_PAGE_LRU Table

The INNODB_BUFFER_PAGE_LRU table provides 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 INNODB_BUFFER_PAGE_LRU table has the same columns as the INNODB_BUFFER_PAGE table, except that the INNODB_BUFFER_PAGE_LRU table has LRU_POSITION and COMPRESSED columns instead of BLOCK_ID and PAGE_STATE columns.

For related usage information and examples, see InnoDB INFORMATION_SCHEMA Buffer Pool Tables.

Warning

Querying the INNODB_BUFFER_PAGE_LRU table 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.

The INNODB_BUFFER_PAGE_LRU table has these columns:

• POOL_ID

  The buffer pool ID. This is an identifier to distinguish between multiple buffer pool instances.

• LRU_POSITION

  The position of the page in the LRU list.

• SPACE

  The tablespace ID; the same value as INNODB_SYS_TABLES.SPACE.

• PAGE_NUMBER

  The page number.

• PAGE_TYPE

  The page type. The following table shows the permitted values.

  Table 5.4 INNODB_BUFFER_PAGE_LRU.PAGE_TYPE Values

  Page Type	Description
  ALLOCATED	Freshly allocated page
  BLOB	Uncompressed BLOB page
  COMPRESSED_BLOB2	Subsequent comp BLOB page
  COMPRESSED_BLOB	First compressed BLOB page
  EXTENT_DESCRIPTOR	Extent descriptor page
  FILE_SPACE_HEADER	File space header
  IBUF_BITMAP	Insert buffer bitmap
  IBUF_FREE_LIST	Insert buffer free list
  IBUF_INDEX	Insert buffer index
  INDEX	B-tree node
  INODE	Index node
  RTREE_INDEX	R-tree index
  SYSTEM	System page
  TRX_SYSTEM	Transaction system data
  UNDO_LOG	Undo log page
  UNKNOWN	Unknown

  
  

• FLUSH_TYPE

  The flush type.

• FIX_COUNT

  The number of threads using this block within the buffer pool. When zero, the block is eligible to be evicted.

• IS_HASHED

  Whether a hash index has been built on this page.

• NEWEST_MODIFICATION

  The Log Sequence Number of the youngest modification.

• OLDEST_MODIFICATION

  The Log Sequence Number of the oldest modification.

• ACCESS_TIME

  An abstract number used to judge the first access time of the page.

• TABLE_NAME

  The name of the table the page belongs to. This column is applicable only to pages with a PAGE_TYPE value of INDEX.

• INDEX_NAME

  The 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_TYPE value of INDEX.

• NUMBER_RECORDS

  The number of records within the page.

• DATA_SIZE

  The sum of the sizes of the records. This column is applicable only to pages with a PAGE_TYPE value of INDEX.

• COMPRESSED_SIZE

  The compressed page size. NULL for pages that are not compressed.

• COMPRESSED

  Whether the page is compressed.

• IO_FIX

  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

  Whether 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. 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

Notes

• This table is useful primarily for expert-level performance monitoring, or when developing performance-related extensions for MySQL.

• You must have the PROCESS privilege to query this table.

• Use the INFORMATION_SCHEMA COLUMNS table or the SHOW COLUMNS statement 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.

• Querying this table requires MySQL to lock the data structure representing the buffer pool while traversing the LRU list, which can reduce concurrency, especially for systems with multi-gigabyte buffer pools.

• When tables, table rows, partitions, or indexes are deleted, associated pages remain in the buffer pool until space is required for other data. The INNODB_BUFFER_PAGE_LRU table reports information about these pages until they are evicted from the buffer pool. For more information about how the InnoDB manages buffer pool data, see Buffer Pool.