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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 |
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.
