25.39.2 The INFORMATION_SCHEMA INNODB_BUFFER_PAGE_LRU Table

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_TABLES.SPACE.

PAGE_NUMBER

The page number.

PAGE_TYPE

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

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.

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 Section 15.5.1, “Buffer Pool”.