Table of Contents
Patch innodb_buffer_pool_pages
The 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 information_schema with information of buffer_pool content
Tables provided
INNODB_BUFFER_POOL_PAGES
allocated pages in buffer pool and current state of them
| Field | Notes |
|---|---|
| page_type | type of the page |
| space_id | space id of the page (position of the page) |
| page_no | page number of the page (position of the page) |
| 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 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
index pages located in buffer_pool
| Field | Notes |
|---|---|
| schema_name | database name |
| table_name | table name |
| index_name | 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) |
| accessed | 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
mysql> select * from information_schema.INNODB_BUFFER_POOL_PAGES_INDEX LIMIT 20; +-------------+------------------+------------+----------+---------+--------+-----------+--------+----------+----------+-------+-----+--------------+-----------+------------+ | schema_name | table_name | index_name | space_id | page_no | n_recs | data_size | hashed | accessed | modified | dirty | old | lru_position | fix_count | flush_type | +-------------+------------------+------------+----------+---------+--------+-----------+--------+----------+----------+-------+-----+--------------+-----------+------------+ | NULL | SYS_IBUF_TABLE_0 | CLUST_IND | 0 | 4 | 2 | 158 | 0 | 1 | 1 | 1 | 0 | 1862158 | 0 | 2 | | NULL | SYS_INDEXES | CLUST_IND | 0 | 11 | 8 | 232 | 0 | 1 | 0 | 0 | 0 | 1862221 | 0 | 0 | | NULL | SYS_INDEXES | CLUST_IND | 0 | 12956 | 186 | 12344 | 1 | 1 | 0 | 0 | 0 | 1862676 | 0 | 0 | | NULL | SYS_TABLES | CLUST_IND | 0 | 8 | 3 | 79 | 0 | 1 | 0 | 0 | 0 | 1862215 | 0 | 0 | | NULL | SYS_TABLES | CLUST_IND | 0 | 288 | 118 | 9456 | 1 | 1 | 0 | 0 | 0 | 1873606 | 0 | 0 | | NULL | SYS_TABLES | CLUST_IND | 0 | 290 | 174 | 13654 | 1 | 1 | 1 | 1 | 0 | 1862216 | 0 | 2 | | NULL | SYS_TABLES | CLUST_IND | 0 | 304 | 151 | 11617 | 0 | 1 | 0 | 0 | 0 | 1864252 | 0 | 0 | | NULL | SYS_COLUMNS | CLUST_IND | 0 | 10 | 28 | 700 | 0 | 1 | 0 | 0 | 0 | 1862217 | 0 | 0 | | NULL | SYS_COLUMNS | CLUST_IND | 0 | 12973 | 194 | 13116 | 0 | 1 | 0 | 0 | 1 | 20 | 0 | 0 | | NULL | SYS_TABLES | ID_IND | 0 | 9 | 443 | 14792 | 0 | 1 | 1 | 1 | 0 | 1863787 | 0 | 2 | | NULL | SYS_FIELDS | CLUST_IND | 0 | 12 | 9 | 225 | 0 | 1 | 0 | 0 | 0 | 1862677 | 0 | 0 | | NULL | SYS_IBUF_TABLE_0 | CLUST_IND | 0 | 45 | 49 | 3554 | 0 | 0 | 0 | 0 | 1 | 55 | 0 | 0 | | NULL | SYS_FOREIGN | ID_IND | 0 | 46 | 20 | 1496 | 0 | 0 | 0 | 0 | 1 | 56 | 0 | 0 | | NULL | SYS_FOREIGN | FOR_IND | 0 | 47 | 20 | 824 | 0 | 1 | 0 | 0 | 0 | 1862224 | 0 | 0 | | NULL | SYS_FOREIGN | REF_IND | 0 | 48 | 20 | 814 | 0 | 1 | 0 | 0 | 0 | 1862225 | 0 | 0 | | NULL | SYS_FOREIGN_COLS | ID_IND | 0 | 49 | 42 | 2583 | 0 | 0 | 0 | 0 | 1 | 59 | 0 | 0 | | NULL | SYS_IBUF_TABLE_0 | CLUST_IND | 0 | 50 | 96 | 7062 | 0 | 0 | 0 | 0 | 1 | 60 | 0 | 0 | | NULL | SYS_IBUF_TABLE_0 | CLUST_IND | 0 | 53 | 65 | 7117 | 0 | 0 | 0 | 0 | 1 | 61 | 0 | 0 | | NULL | SYS_IBUF_TABLE_0 | CLUST_IND | 0 | 54 | 94 | 6110 | 0 | 0 | 0 | 0 | 1 | 62 | 0 | 0 | | NULL | SYS_IBUF_TABLE_0 | CLUST_IND | 0 | 55 | 40 | 2680 | 0 | 0 | 0 | 0 | 1 | 63 | 0 | 0 | +-------------+------------------+------------+----------+---------+--------+-----------+--------+----------+----------+-------+-----+--------------+-----------+------------+ 20 rows in set (12.99 sec)
INNODB_BUFFER_POOL_PAGES_BLOB
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)
Except where otherwise noted, content on this wiki is licensed under the following license:GNU Free Documentation License 1.2
