28.4.2 The INFORMATION_SCHEMA INNODB_BUFFER_PAGE Table (original) (raw)

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, seeSection 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 asINNODB_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 isNULL 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 ofINDEX.
• 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 theSHOW 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. TheINNODB_BUFFER_PAGE table reports information about these pages until they are evicted from the buffer pool. For more information about how theInnoDB manages buffer pool data, seeSection 17.5.1, “Buffer Pool”.