28.4.2 The INFORMATION_SCHEMA INNODB_BUFFER_PAGE Table

Skip to Main Content

MySQL 8.4 Reference Manual Including MySQL NDB Cluster 8.4

28.4.2 The INFORMATION_SCHEMA INNODB_BUFFER_PAGE Table

The INNODB_BUFFER_PAGE table provides information about each page in the InnoDB buffer pool.

For related usage information and examples, see Section 17.15.5, “InnoDB INFORMATION_SCHEMA Buffer Pool Tables”.

Warning

Querying the INNODB_BUFFER_PAGE 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 table has these columns:

  • POOL_ID

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

The buffer pool block ID. - 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.

Table 28.4 INNODB_BUFFER_PAGE.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
ENCRYPTED_RTREE Encrypted R-tree
EXTENT_DESCRIPTOR Extent descriptor page
FILE_SPACE_HEADER File space header
FIL_PAGE_TYPE_UNUSED Unused
IBUF_BITMAP Insert buffer bitmap
IBUF_FREE_LIST Insert buffer free list
IBUF_INDEX Insert buffer index
INDEX B-tree node
INODE Index node
LOB_DATA Uncompressed LOB data
LOB_FIRST First page of uncompressed LOB
LOB_INDEX Uncompressed LOB index
PAGE_IO_COMPRESSED Compressed page
PAGE_IO_COMPRESSED_ENCRYPTED Compressed and encrypted page
PAGE_IO_ENCRYPTED Encrypted page
RSEG_ARRAY Rollback segment array
RTREE_INDEX R-tree index
SDI_BLOB Uncompressed SDI BLOB
SDI_COMPRESSED_BLOB Compressed SDI BLOB
SDI_INDEX SDI index
SYSTEM System page
TRX_SYSTEM Transaction system data
UNDO_LOG Undo log page
UNKNOWN Unknown
ZLOB_DATA Compressed LOB data
ZLOB_FIRST First page of compressed LOB
ZLOB_FRAG Compressed LOB fragment
ZLOB_FRAG_ENTRY Compressed LOB fragment index
ZLOB_INDEX Compressed LOB index
- 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. The column is NULL if the server has not yet accessed the table. - 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. - PAGE_STATE

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

Table 28.5 INNODB_BUFFER_PAGE.PAGE_STATE Values

Page State Description
FILE_PAGE A buffered file page
MEMORY Contains a main memory object
NOT_USED In the free list
NULL Clean compressed pages, compressed pages in the flush list, pages used as buffer pool watch sentinels
READY_FOR_USE A free page
REMOVE_HASH Hash index should be removed before placing in the free list
- 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, IO_PIN = relocation and removal from the flush not permitted. - 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. - IS_STALE

Whether the page is stale.

Example

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE LIMIT 1\G
*************************** 1. row ***************************
            POOL_ID: 0
           BLOCK_ID: 0
              SPACE: 97
        PAGE_NUMBER: 2473
          PAGE_TYPE: INDEX
         FLUSH_TYPE: 1
          FIX_COUNT: 0
          IS_HASHED: YES
NEWEST_MODIFICATION: 733855581
OLDEST_MODIFICATION: 0
        ACCESS_TIME: 3378385672
         TABLE_NAME: `employees`.`salaries`
         INDEX_NAME: PRIMARY
     NUMBER_RECORDS: 468
          DATA_SIZE: 14976
    COMPRESSED_SIZE: 0
         PAGE_STATE: FILE_PAGE
             IO_FIX: IO_NONE
             IS_OLD: YES
    FREE_PAGE_CLOCK: 66
           IS_STALE: NO

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.
  • 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 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 17.5.1, “Buffer Pool”.