What is stored InnoDB buffer pool
Posted by Vadim |
Ever wonder what is stored in InnoDB buffer pool at the moment ?
It is not so hard actually - we made a short patch for MySQL 5.0 which show innodb buffer pool content
SQL:
-
mysql> SELECT * FROM information_schema.INNODB_BUFFER_POOL_CONTENT;
-
+-----------+-------+--------+---------+----------+------------+-----------+--------------+--------------+----------------+-----------------+--------------+------------------+
-
| BLOCK_NUM | SPACE | OFFSET | RECORDS | DATASIZE | FLUSH_TYPE | FIX_COUNT | LRU_POSITION | PAGE_TYPE_ID | PAGE_TYPE | INDEX_NAME | TABLE_SCHEMA | TABLE_NAME |
-
+-----------+-------+--------+---------+----------+------------+-----------+--------------+--------------+----------------+-----------------+--------------+------------------+
-
| 0 | 0 | 7 | 0 | 0 | 0 | 0 | 2 | 6 | unknown | | | |
-
| 1 | 0 | 1 | 0 | 0 | 0 | 0 | 3 | 5 | unknown | | | |
-
| 2 | 0 | 3 | 0 | 0 | 0 | 0 | 4 | 6 | unknown | | | |
-
| 3 | 0 | 2 | 0 | 0 | 0 | 0 | 5 | 3 | inode | | | |
-
| 4 | 0 | 4 | 0 | 0 | 0 | 0 | 6 | 17855 | INDEX | CLUST_IND | | SYS_IBUF_TABLE_0 |
-
| 5 | 0 | 11 | 23 | 1560 | 0 | 0 | 7 | 17855 | INDEX | CLUST_IND | | SYS_INDEXES |
-
| 6 | 0 | 5 | 242 | 0 | 3 | 0 | 8 | 7 | unknown | | | |
-
| 7 | 0 | 6 | 3006 | 0 | 0 | 0 | 9 | 6 | unknown | | | |
-
| 8 | 0 | 45 | 0 | 0 | 0 | 0 | 10 | 2 | undo_log | | | |
-
| 9 | 0 | 50 | 0 | 0 | 0 | 0 | 11 | 2 | undo_log | | | |
-
| 10 | 0 | 8 | 12 | 894 | 0 | 0 | 12 | 17855 | INDEX | CLUST_IND | | SYS_TABLES |
-
| 11 | 0 | 0 | 0 | 0 | 0 | 0 | 13 | 8 | unknown | | | |
-
| 12 | 0 | 10 | 93 | 5864 | 0 | 0 | 14 | 17855 | INDEX | CLUST_IND | | SYS_COLUMNS |
-
| 13 | 0 | 9 | 12 | 354 | 0 | 0 | 15 | 17855 | INDEX | ID_IND | | SYS_TABLES |
-
| 14 | 0 | 12 | 32 | 1313 | 0 | 0 | 16 | 17855 | INDEX | CLUST_IND | | SYS_FIELDS |
-
| 47 | 0 | 46 | 0 | 0 | 0 | 0 | 49 | 17855 | INDEX | ID_IND | | SYS_FOREIGN |
-
| 48 | 0 | 47 | 0 | 0 | 0 | 0 | 50 | 17855 | INDEX | FOR_IND | | SYS_FOREIGN |
-
| 49 | 0 | 48 | 0 | 0 | 0 | 0 | 51 | 17855 | INDEX | REF_IND | | SYS_FOREIGN |
-
| 50 | 0 | 49 | 0 | 0 | 0 | 0 | 52 | 17855 | INDEX | ID_IND | | SYS_FOREIGN_COLS |
-
| 51 | 0 | 51 | 14 | 1589 | 0 | 0 | 53 | 17855 | INDEX | CLUST_IND | | SYS_IBUF_TABLE_0 |
-
| 52 | 0 | 52 | 138 | 16037 | 0 | 0 | 54 | 17855 | INDEX | CLUST_IND | | SYS_IBUF_TABLE_0 |
-
| 53 | 0 | 53 | 72 | 8148 | 0 | 0 | 55 | 17855 | INDEX | CLUST_IND | | SYS_IBUF_TABLE_0 |
-
| 54 | 0 | 54 | 55 | 6313 | 0 | 0 | 56 | 17855 | INDEX | CLUST_IND | | SYS_IBUF_TABLE_0 |
-
| 55 | 0 | 55 | 39 | 3959 | 0 | 0 | 57 | 17855 | INDEX | CLUST_IND | | SYS_IBUF_TABLE_0 |
-
| 56 | 0 | 56 | 24 | 2816 | 0 | 0 | 58 | 17855 | INDEX | CLUST_IND | | SYS_IBUF_TABLE_0 |
-
| 57 | 0 | 57 | 286 | 0 | 0 | 0 | 59 | 4 | ibuf_free_list | | | |
-
| 58 | 0 | 58 | 928 | 0 | 0 | 0 | 60 | 4 | ibuf_free_list | | | |
-
| 59 | 0 | 59 | 64 | 0 | 0 | 0 | 61 | 4 | ibuf_free_list | | | |
-
| 60 | 0 | 60 | 132 | 0 | 0 | 0 | 62 | 4 | ibuf_free_list | | | |
-
| 61 | 0 | 61 | 69 | 0 | 0 | 0 | 63 | 4 | ibuf_free_list | | | |
-
| 62 | 0 | 62 | 44 | 0 | 0 | 0 | 64 | 4 | ibuf_free_list | | | |
-
| 63 | 0 | 63 | 44 | 0 | 0 | 0 | 65 | 2 | undo_log | | | |
-
| 64 | 17 | 3 | 4 | 60 | 0 | 0 | 66 | 17855 | INDEX | GEN_CLUST_INDEX | art | a87 |
-
| 66 | 17 | 2 | 0 | 0 | 0 | 0 | 68 | 3 | inode | | | |
-
| 67 | 17 | 38 | 1048 | 15720 | 0 | 0 | 69 | 17855 | INDEX | GEN_CLUST_INDEX | art | a87 |
-
| 68 | 17 | 1715 | 128 | 15077 | 0 | 0 | 70 | 17855 | INDEX | GEN_CLUST_INDEX | art | a87 |
-
| 69 | 17 | 37 | 1048 | 15720 | 0 | 0 | 71 | 17855 | INDEX | GEN_CLUST_INDEX | art | a87 |
-
| 70 | 17 | 886 | 134 | 15147 | 0 | 0 | 72 | 17855 | INDEX | GEN_CLUST_INDEX | art | a87 |
-
| 71 | 17 | 36 | 524 | 7860 | 0 | 0 | 73 | 17855 | INDEX | GEN_CLUST_INDEX | art | a87 |
-
| 72 | 17 | 29 | 137 | 15085 | 0 | 0 | 74 | 17855 | INDEX | GEN_CLUST_INDEX | art | a87 |
-
| 73 | 17 | 39 | 155 | 2325 | 0 | 0 | 75 | 17855 | INDEX | GEN_CLUST_INDEX | art | a87 |
-
| 74 | 17 | 2670 | 130 | 15114 | 0 | 0 | 76 | 17855 | INDEX | GEN_CLUST_INDEX | art | a87 |
-
| 75 | 17 | 2591 | 117 | 15112 | 0 | 0 | 77 | 17855 | INDEX | GEN_CLUST_INDEX | art | a87 |
-
| 76 | 17 | 714 | 146 | 15067 | 0 | 0 | 78 | 17855 | INDEX | GEN_CLUST_INDEX | art | a87 |
-
| 77 | 17 | 409 | 142 | 15110 | 0 | 0 | 79 | 17855 | INDEX | GEN_CLUST_INDEX | art | a87 |
-
| 78 | 17 | 2739 | 133 | 15110 | 0 | 0 | 80 | 17855 | INDEX | GEN_CLUST_INDEX | art | a87 |
-
| 79 | 1 | 3 | 3 | 39 | 0 | 0 | 81 | 17855 | INDEX | PRIMARY | art | author87 |
-
| 80 | 1 | 1 | 0 | 0 | 0 | 0 | 51 | 5 | unknown | | | |
-
| 81 | 1 | 2 | 0 | 0 | 0 | 0 | 51 | 3 | inode | | | |
-
| 82 | 1 | 137 | 807 | 10491 | 0 | 0 | 51 | 17855 | INDEX | PRIMARY | art | author87 |
-
| 83 | 1 | 3491 | 140 | 15095 | 0 | 0 | 51 | 17855 | INDEX | PRIMARY | art | author87 |
-
| 84 | 1 | 3799 | 139 | 15144 | 0 | 0 | 51 | 17855 | INDEX | PRIMARY | art | author87 |
-
| 85 | 1 | 2852 | 173 | 15070 | 0 | 0 | 51 | 17855 | INDEX | PRIMARY | art | author87 |
-
| 86 | 1 | 3096 | 167 | 15054 | 0 | 0 | 51 | 17855 | INDEX | PRIMARY | art | author87 |
-
| 87 | 1 | 3340 | 286 | 15082 | 0 | 0 | 51 | 17855 | INDEX | PRIMARY | art | author87 |
-
| 88 | 1 | 3648 | 138 | 15127 | 0 | 0 | 51 | 17855 | INDEX | PRIMARY | art | author87 |
-
| 89 | 1 | 3892 | 151 | 15088 | 0 | 0 | 51 | 17855 | INDEX | PRIMARY | art | author87 |
-
| 90 | 1 | 3009 | 187 | 15119 | 0 | 0 | 51 | 17855 | INDEX | PRIMARY | art | author87 |
-
| 91 | 1 | 4 | 3 | 102 | 0 | 0 | 51 | 17855 | INDEX | site_id | art | author87 |
-
| 92 | 1 | 138 | 360 | 11649 | 0 | 0 | 51 | 17855 | INDEX | site_id | art | author87 |
-
| 93 | 1 | 3255 | 354 | 9201 | 0 | 0 | 51 | 17855 | INDEX | site_id | art | author87 |
-
| 94 | 1 | 1534 | 309 | 10506 | 0 | 0 | 51 | 17855 | INDEX | site_id | art | author87 |
-
| 95 | 1 | 3440 | 328 | 8198 | 0 | 0 | 51 | 17855 | INDEX | site_id | art | author87 |
-
| 96 | 1 | 479 | 498 | 11689 | 0 | 0 | 51 | 17855 | INDEX | site_id | art | author87 |
-
| 97 | 1 | 3246 | 346 | 8981 | 0 | 0 | 51 | 17855 | INDEX | site_id | art | author87 |
-
| 98 | 1 | 2518 | 427 | 11450 | 0 | 0 | 51 | 17855 | INDEX | site_id | art | author87 |
-
| 99 | 1 | 2070 | 316 | 8273 | 0 | 0 | 51 | 17855 | INDEX | site_id | art | author87 |
-
| 100 | 1 | 3259 | 324 | 8988 | 0 | 0 | 51 | 17855 | INDEX | site_id | art | author87 |
-
| 101 | 1 | 135 | 601 | 7813 | 0 | 0 | 51 | 17855 | INDEX | PRIMARY | art | author87 |
-
| 102 | 1 | 5 | 140 | 7812 | 0 | 0 | 51 | 17855 | INDEX | PRIMARY | art | author87 |
-
| 103 | 1 | 6 | 286 | 15067 | 0 | 0 | 51 | 17855 | INDEX | PRIMARY | art | author87 |
-
| 122 | 1 | 32 | 94 | 15024 | 0 | 0 | 51 | 17855 | INDEX | PRIMARY | art | author87 |
-
+-----------+-------+--------+---------+----------+------------+-----------+--------------+--------------+----------------+-----------------+--------------+------------------+
-
90 rows IN SET (0.01 sec
The binaries, RPMS (RedHat/Centos) and full source code also available to download
P.S. There is Jeremy Cole's patch for InnoDB plugin MySQL 5.1, and main idea was taken from there.
del.icio.us
digg
Related posts: :Quickly preloading Innodb tables in the buffer pool::Few more ideas for InnoDB features::More Gotchas with MySQL 5.0:
6 Comments
This is great! Can we expect to see this coming out in the Percona 5.1 binaries as well?
Comment :: September 29, 2008 @ 5:16 am
Is this patch also for the 5.0.68 Percona release available?
Comment :: September 29, 2008 @ 9:01 am
Gregory:
I think in 5.1 we will include Jeremey Cole’s patch
Comment :: September 29, 2008 @ 9:14 am
Nils,
It is only for 5.0.67, will release for 5.0.68 later
Comment :: September 29, 2008 @ 9:15 am
I am looking for a similar tool for the ibdata and ibd files – somethings that dumps page numbers and for each page tells me what the page is and does.
Comment :: March 16, 2009 @ 1:56 am
Isotopp,
It is on our todo, will implement it when we can
Comment :: March 16, 2009 @ 10:24 am