]]>
Call us: 1-888-316-9775 • Contact Us
MySQL and InnoDB are trademarks of Oracle Corp.
Proudly running Percona Server
Copyright © 2006-2012 Percona Inc.
Copyright, Trademark, and Privacy Policy • Sitemap
]]>
You were redirected here from percona-xtradb:patch:innodb_buffer_pool_pages.
Buffer Pool Contents Information
Description
This idea was taken from Jeremy Cole's patch http://provenscaling.com/patches/innodb_plugin/1.0/draft/i_s_innodb_buffer_pool_pages.patch
We add several tables to the INFORMATION_SCHEMA with information about the buffer pool contents.
INFORMATION_SCHEMA Tables
INNODB_BUFFER_POOL_PAGES
This table shows the characteristics of the allocated pages in buffer pool and current state of them.
| Field | Notes |
|---|---|
| page_type | Type of the page. Possible values: index, undo_log, inode, ibuf_free_list, allocated, bitmap, sys, trx_sys, fsp_hdr, xdes, blob, zblob, zblob2, unknown |
| space_id | The id of the table in the tablespace containing it |
| page_no | page number in the buffer pool of the page (position of the page) |
| lru_position | lru clock of the block |
| fix_count | |
| flush_type | type of the last flush of the page (0:LRU 2:flush_list) |
Example
mysql> select * from information_schema.INNODB_BUFFER_POOL_PAGES LIMIT 20; +-----------+----------+---------+--------------+-----------+------------+ | page_type | space_id | page_no | lru_position | fix_count | flush_type | +-----------+----------+---------+--------------+-----------+------------+ | allocated | 0 | 7 | 3 | 0 | 2 | | allocated | 0 | 1 | 4 | 0 | 0 | | allocated | 0 | 3 | 5 | 0 | 0 | | inode | 0 | 2 | 6 | 0 | 2 | | index | 0 | 4 | 7 | 0 | 2 | | index | 0 | 11 | 8 | 0 | 0 | | index | 0 | 12956 | 9 | 0 | 0 | | allocated | 0 | 5 | 10 | 0 | 2 | | allocated | 0 | 6 | 11 | 0 | 2 | | undo_log | 0 | 51 | 12 | 0 | 2 | | undo_log | 0 | 52 | 13 | 0 | 2 | | index | 0 | 8 | 14 | 0 | 0 | | index | 0 | 288 | 15 | 0 | 0 | | index | 0 | 290 | 16 | 0 | 2 | | index | 0 | 304 | 17 | 0 | 0 | | allocated | 0 | 0 | 18 | 0 | 2 | | index | 0 | 10 | 19 | 0 | 0 | | index | 0 | 12973 | 20 | 0 | 0 | | index | 0 | 9 | 21 | 0 | 2 | | index | 0 | 12 | 22 | 0 | 0 | +-----------+----------+---------+--------------+-----------+------------+ 20 rows in set (0.81 sec)
INNODB_BUFFER_POOL_PAGES_INDEX
This table shows information about the index pages located in the buffer pool.
| Field | Notes |
|---|---|
| index_id | index name |
| space_id | space id of the page (position of the page) |
| page_no | page number of the page (position of the page) |
| n_recs | number of user records on page |
| data_size | sum of the sizes of the records in page |
| hashed | the block is in adaptive hash index (1) or not (0) |
| access_time | used (1) or not (0) |
| modified | modified since loaded (1) or not (0) |
| dirty | modified since last flushed (1) or not (0) |
| old | is old blocks in the LRU list (1) or not (0) |
| lru_position | lru clock of the block |
| fix_count | how many in use |
| flush_type | type of the last flush of the page (0:LRU 2:flush_list) |
Example
+----------+----------+---------+--------+-----------+--------+-------------+----------+-------+-----+--------------+-----------+------------+ | index_id | space_id | page_no | n_recs | data_size | hashed | access_time | modified | dirty | old | lru_position | fix_count | flush_type | +----------+----------+---------+--------+-----------+--------+-------------+----------+-------+-----+--------------+-----------+------------+ | 39 | 0 | 5787 | 468 | 14976 | 1 | 2636182517 | 1 | 0 | 1 | 0 | 0 | 2 | | 40 | 0 | 5647 | 1300 | 15600 | 1 | 2636182517 | 1 | 0 | 1 | 0 | 0 | 2 | | 39 | 0 | 5786 | 468 | 14976 | 1 | 2636182516 | 1 | 0 | 1 | 0 | 0 | 2 | | 40 | 0 | 6938 | 1300 | 15600 | 1 | 2636193968 | 1 | 0 | 1 | 0 | 0 | 2 | | 39 | 0 | 5785 | 468 | 14976 | 1 | 2636182514 | 1 | 0 | 1 | 0 | 0 | 2 | | 39 | 0 | 5784 | 468 | 14976 | 1 | 2636182512 | 1 | 0 | 1 | 0 | 0 | 2 | | 40 | 0 | 5646 | 1300 | 15600 | 1 | 2636182511 | 1 | 0 | 1 | 0 | 0 | 2 | | 39 | 0 | 7203 | 468 | 14976 | 1 | 2636193967 | 1 | 0 | 1 | 0 | 0 | 2 | | 39 | 0 | 5783 | 468 | 14976 | 1 | 2636182507 | 1 | 0 | 1 | 0 | 0 | 2 | | 39 | 0 | 5782 | 468 | 14976 | 1 | 2636182506 | 1 | 0 | 1 | 0 | 0 | 2 | +----------+----------+---------+--------+-----------+--------+-------------+----------+-------+-----+--------------+-----------+------------+
INNODB_BUFFER_POOL_PAGES_BLOB
This table shows information from blob pages located in buffer pool.
| Field | Notes |
|---|---|
| space_id | space id of the page (position of the page) |
| page_no | page number of the page (position of the page) |
| compressed | contains compressed data (1) or not (0) |
| part_len | data length in the page |
| next_page_no | page number of the next data |
| lru_position | lru clock of the block |
| fix_count | how many in use |
| flush_type | type of the last flush of the page (0:LRU 2:flush_list) |
Example
mysql> select * from information_schema.INNODB_BUFFER_POOL_PAGES_BLOB LIMIT 20; +----------+---------+------------+----------+--------------+--------------+-----------+------------+ | space_id | page_no | compressed | part_len | next_page_no | lru_position | fix_count | flush_type | +----------+---------+------------+----------+--------------+--------------+-----------+------------+ | 1748 | 111 | 0 | 10137 | 0 | 263 | 0 | 2 | | 1748 | 307 | 0 | 5210 | 0 | 1084 | 0 | 2 | | 1748 | 1329 | 0 | 6146 | 0 | 4244 | 0 | 2 | | 1748 | 1330 | 0 | 11475 | 0 | 4245 | 0 | 2 | | 1748 | 1345 | 0 | 5550 | 0 | 4247 | 0 | 2 | | 1748 | 1346 | 0 | 7597 | 0 | 4248 | 0 | 2 | | 1748 | 3105 | 0 | 6716 | 0 | 8919 | 0 | 2 | | 1748 | 3213 | 0 | 8170 | 0 | 9390 | 0 | 2 | | 1748 | 6142 | 0 | 5648 | 0 | 19638 | 0 | 2 | | 1748 | 7387 | 0 | 10634 | 0 | 24191 | 0 | 2 | | 1748 | 7426 | 0 | 5355 | 0 | 24194 | 0 | 2 | | 1748 | 7489 | 0 | 16330 | 7489 | 24196 | 0 | 2 | | 1748 | 7490 | 0 | 7126 | 0 | 24197 | 0 | 2 | | 1748 | 7657 | 0 | 13571 | 0 | 24681 | 0 | 2 | | 1748 | 7840 | 0 | 11208 | 0 | 25737 | 0 | 2 | | 1748 | 9599 | 0 | 11882 | 0 | 31989 | 0 | 2 | | 1748 | 11719 | 0 | 7367 | 0 | 40466 | 0 | 2 | | 1748 | 12051 | 0 | 11049 | 0 | 41441 | 0 | 2 | | 1748 | 12052 | 0 | 16330 | 12052 | 41442 | 0 | 2 | | 1748 | 12053 | 0 | 2674 | 0 | 41443 | 0 | 2 | +----------+---------+------------+----------+--------------+--------------+-----------+------------+ 20 rows in set (0.05 sec)
INNODB_BUFFER_POOL_CONTENT
This table has been deprecated due to performance issues. Similar information can be obtained combining the information from the tables above and INNODB_SYS_*.
| 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 */
Except where otherwise noted, content on this wiki is licensed under the following license:CC Attribution-Noncommercial-Share Alike 3.0 Unported
