Documentation Home
MySQL 5.6 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 31.0Mb
PDF (A4) - 31.0Mb
PDF (RPM) - 29.3Mb
HTML Download (TGZ) - 7.2Mb
HTML Download (Zip) - 7.2Mb
HTML Download (RPM) - 6.2Mb
Man Pages (TGZ) - 175.7Kb
Man Pages (Zip) - 285.9Kb
Info (Gzip) - 3.0Mb
Info (Zip) - 3.0Mb
Excerpts from this Manual

MySQL 5.6 Reference Manual  /  ...  /  The INFORMATION_SCHEMA INNODB_BUFFER_PAGE Table


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

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


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.

Table 21.1 INNODB_BUFFER_PAGE Columns

Column name Description
POOL_ID Buffer Pool ID. An identifier to distinguish between multiple buffer pool instances.
BLOCK_ID Buffer Pool Block ID.
SPACE Tablespace ID. Uses the same value as in INNODB_SYS_TABLES.SPACE.
PAGE_NUMBER Page number.
PAGE_TYPE Page type. Permitted values are ALLOCATED (Freshly allocated page), INDEX (B-tree node), UNDO_LOG (Undo log page), INODE (Index node), IBUF_FREE_LIST (Insert buffer free list), IBUF_BITMAP (Insert buffer bitmap), SYSTEM (System page), TRX_SYSTEM (Transaction system data), FILE_SPACE_HEADER (File space header), EXTENT_DESCRIPTOR (Extent descriptor page), BLOB (Uncompressed BLOB page), COMPRESSED_BLOB (First compressed BLOB page), COMPRESSED_BLOB2 (Subsequent comp BLOB page), IBUF_INDEX (Insert buffer index), UNKNOWN (unknown).
FLUSH_TYPE Flush type.
FIX_COUNT 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. Possible values include: NULL (clean compressed pages, compressed pages in the flush list, pages used as buffer pool watch sentinels), NOT_USED (in the free list), READY_FOR_USE (a free page), FILE_PAGE (a buffered file page), MEMORY (contains a main memory object), REMOVE_HASH (hash index should be removed before placing in the free list).
IO_FIX Specifies whether any I/O is pending for this page: IO_NONE = no pending I/O, IO_READ = read pending, IO_WRITE = write pending.
IS_OLD Specifies whether or not 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.


*************************** 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
        ACCESS_TIME: 3378385672
         TABLE_NAME: `employees`.`salaries`
          DATA_SIZE: 14976
             IO_FIX: IO_NONE
             IS_OLD: YES
1 row in set (0.03 sec)


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

  • Use DESCRIBE or SHOW COLUMNS to view additional information about the columns of this table including data types and default values.

  • You must have the PROCESS privilege to query this table.

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

User Comments
Sign Up Login You must be logged in to post a comment.