provides information about the pages in the
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.
INNODB_BUFFER_PAGE_LRU table has
the same columns as the
INNODB_BUFFER_PAGE table with a few
exceptions. It has
COMPRESSED columns instead of
columns, and it does not include and
For related usage information and examples, see Section 15.15.5, “InnoDB INFORMATION_SCHEMA Buffer Pool Tables”.
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
INNODB_BUFFER_PAGE_LRU table has
The buffer pool ID. This is an identifier to distinguish between multiple buffer pool instances.
The position of the page in the LRU list.
The tablespace ID; the same value as
The page number.
The page type. The following table shows the permitted values.
Table 26.6 INNODB_BUFFER_PAGE_LRU.PAGE_TYPE Values
Page Type Description
Freshly allocated page
Uncompressed BLOB page
Subsequent comp BLOB page
First compressed BLOB page
Extent descriptor page
File space header
Insert buffer bitmap
Insert buffer free list
Insert buffer index
Uncompressed LOB data
First page of uncompressed LOB
Uncompressed LOB index
Compressed and encrypted page
Rollback segment array
Uncompressed SDI BLOB
Compressed SDI BLOB
Transaction system data
Undo log page
Compressed LOB data
First page of compressed LOB
Compressed LOB fragment
Compressed LOB fragment index
Compressed LOB index
The flush type.
The number of threads using this block within the buffer pool. When zero, the block is eligible to be evicted.
Whether a hash index has been built on this page.
The Log Sequence Number of the youngest modification.
The Log Sequence Number of the oldest modification.
An abstract number used to judge the first access time of the page.
The name of the table the page belongs to. This column is applicable only to pages with a
INDEX. The column is
NULLif the server has not yet accessed the table.
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
The number of records within the page.
The sum of the sizes of the records. This column is applicable only to pages with a
The compressed page size.
NULLfor pages that are not compressed.
Whether the page is compressed.
Whether any I/O is pending for this page:
IO_NONE= no pending I/O,
IO_READ= read pending,
IO_WRITE= write pending.
Whether the block is in the sublist of old blocks in the LRU list.
The value of the
freed_page_clockcounter when the block was the last placed at the head of the LRU list. The
freed_page_clockcounter tracks the number of blocks removed from the end of the LRU list.
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
This table is useful primarily for expert-level performance monitoring, or when developing performance-related extensions for MySQL.
You must have the
PROCESSprivilege to query this table.
COLUMNStable or the
SHOW COLUMNSstatement 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_LRUtable reports information about these pages until they are evicted from the buffer pool. For more information about how the
InnoDBmanages buffer pool data, see Section 15.5.1, “Buffer Pool”.