Documentation Home
MySQL Information Schema
Related Documentation Download this Excerpt
PDF (US Ltr) - 0.7Mb
PDF (A4) - 0.7Mb


MySQL Information Schema  /  INFORMATION_SCHEMA InnoDB Tables  /  The INFORMATION_SCHEMA INNODB_BUFFER_PAGE Table

5.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 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 5.2 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 5.3 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. Added in MySQL 8.0.24.

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 Buffer Pool.