21.29.17 The INFORMATION_SCHEMA INNODB_BUFFER_PAGE_LRU Table

The INNODB_BUFFER_PAGE_LRU table holds
information about the pages in the InnoDBbuffer 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.

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.

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

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_USE,
NOT_USED,
REMOVE_HASH.

Notes

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

You must have the PROCESS privilege to
query this table.

Use DESCRIBE or
SHOW COLUMNS 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 time 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 8.9.1, “The InnoDB Buffer Pool”.