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

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

FIXME

FieldNotes
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

FieldNotes
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

FieldNotes
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)
 
percona-xtradb/patch/innodb_buffer_pool_pages.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
]]> ]]>