]]>
]]>
This page has been moved, the new location is Buffer Pool Contents Information.

Patch: innodb_show_bp

This patch adds the INNODB_BUFFER_POOL_CONTENT table in the INFORMATION_SCHEMA. The table contains information about the pages in the InnoDB buffer pool.

Variables Provided

This patch provides no variables.

Tables Provided

The patch provides the following INFORMATION_SCHEMA tables:

INNODB_BUFFER_POOL_CONTENT

This table contains information about the pages in the InnoDB buffer pool. The columns are as follows:

Field Notes
BLOCK_NUM A number that increments as the buffer pool is scanned.
SPACE The tablespace number.
OFFSET The page's offset in the file.
RECORDS The number of records in the page.
DATASIZE The size of the data in the page.
FLUSH_TYPE A flag that's set if the page is being flushed. One of BUF_FLUSH_LRU, BUF_FLUSH_SINGLE_PAGE, BUF_FLUSH_LIST.
FIX_COUNT A count of how manyfold this block is currently bufferfixed.
LRU_POSITION A value which monotonically decreases.
PAGE_TYPE_ID The page type.
PAGE_TYPE The page type as an enumerated constant. Possible values are ibuf_free_list (insert buffer free list), index (this is an index page), inode, undo_log (this is an old row version for MVCC and rollbacks).
INDEX_NAME The name of the index, if it's an index page.
TABLE_SCHEMA The name of the schema, if it's an index page.
TABLE_NAME The name of the table, if it's an index page.

Example

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_BUFFER_POOL_CONTENT LIMIT 1\G
*************************** 1. row ***************************
   BLOCK_NUM: 0
       SPACE: 749
      OFFSET: 39441
     RECORDS: 100
    DATASIZE: 7400
  FLUSH_TYPE: 3
   FIX_COUNT: 0
LRU_POSITION: 11346860
PAGE_TYPE_ID: 17855
   PAGE_TYPE: index
  INDEX_NAME: PRIMARY
TABLE_SCHEMA: test
  TABLE_NAME: test_table

You may see different page_type, here is info from source code:

/** File page types (values of FIL_PAGE_TYPE) @{ */
#define FIL_PAGE_INDEX          17855   /*!< B-tree node */
#define FIL_PAGE_UNDO_LOG       2       /*!< Undo log page */
#define FIL_PAGE_INODE          3       /*!< Index node */
#define FIL_PAGE_IBUF_FREE_LIST 4       /*!< Insert buffer free list */
/* File page types introduced in MySQL/InnoDB 5.1.7 */
#define FIL_PAGE_TYPE_ALLOCATED 0       /*!< Freshly allocated page */
#define FIL_PAGE_IBUF_BITMAP    5       /*!< Insert buffer bitmap */
#define FIL_PAGE_TYPE_SYS       6       /*!< System page */
#define FIL_PAGE_TYPE_TRX_SYS   7       /*!< Transaction system data */
#define FIL_PAGE_TYPE_FSP_HDR   8       /*!< File space header */
#define FIL_PAGE_TYPE_XDES      9       /*!< Extent descriptor page */
#define FIL_PAGE_TYPE_BLOB      10      /*!< Uncompressed BLOB page */
#define FIL_PAGE_TYPE_ZBLOB     11      /*!< First compressed BLOB page */
#define FIL_PAGE_TYPE_ZBLOB2    12      /*!< Subsequent compressed BLOB page */

Patch Information

Author/OriginPercona, inspired by Jeremy Cole's work
Bugs fixed
Dependencies
 
patches/innodb_show_bp.txt · Last modified: 2011/07/17 21:05 (external edit)
Except where otherwise noted, content on this wiki is licensed under the following license:CC Attribution-Noncommercial-Share Alike 3.0 Unported
Contact Us 24 Hours A Day
SupportContact us 24×7
Emergency? Contact us for help now!
Sales North America(888) 316-9775 or
(208) 473-2904
Sales
Europe
+44-208-133-0309 (UK)
0-800-051-8984 (UK)
0-800-181-0665 (GER)
Training(855) 55TRAIN or
(925) 271-5054

 

Share This
]]> ]]>