Shulou(Shulou.com)06/01 Report--

Innblock | InnoDB page observation tool special thanks to the author, who is an early student of Zhi Ju Tang. The initial idea of writing such a tool was also recognized and encouraged by teacher Ye Jinrong. This idea also consumed several nights of rest. I would like to thank teacher Ye Jinrong again for reviewing the tools. Teacher Ye's experience and knowledge are the precious wealth of every student. Thanks to the main authors Zhou Yanwei, Wang Zhufeng and Qiang Changjin for their recognition and authorization to quote some of the contents of this tool. If you feel difficult about the content of this article, you can refer to the book by yourself, or you can use it with some chapters in the book. Thanks to my colleagues Dai Zhengyong, Yang Haibo, Tian Xingchun and Zou Qijian in the DBA team of Yijifu Company. I took on most of the database-related work when I studied and wrote the code. I am honored to meet you in Chongqing. You are the best. Tool acquisition

Baidu cloud disk address:

Http://pan.baidu.com/s/1qYnyVWo

I. Preface

The internal organization of index blocks in InnoDB has always been something that everyone is interested in and willing to study. We can feel that the author has spent a lot of space describing what is slot, what is heap, and what is the logical and physical order of records from many books and articles.

But it is difficult to see intuitively, because the data file is a binary file. Although we can view it through similar commands such as LINUX's hexdump, it is difficult to extract all kinds of useful information intuitively with a large amount of hexadecimal information. I believe many people view it through the naked eye like the author, but this is obviously a laborious and thankless method.

In Oracle, we can view the information of block through the method of dump block, so can InnoDB do the same?

In line with the purpose of making people more intuitive to observe the information of the underlying index blocks, the author directly borrows a variety of macro definitions in the source code, using C++ and STL list container to achieve such a tool innblock. Due to work reasons, I can't devote myself to writing the code, so the code is a little messy. So if there is bug, please forgive me and put forward it, the author will update it as soon as possible, thank you.

Agreement

Index page (index page, index block), the InnoDB table is an index organization table based on a clustered index, and the whole table is either a clustered index or a general index. Therefore, in the InnoDB tablespace file, the data page is also an index page, so we collectively call it the index page, which is represented by page no in English.

II. Brief introduction of innblock

This tool has two functions.

The first scan function is used to find all index pages in the ibd file.

The second analyze function is used to scan the row data in the data block.

Take a look at the help output first

-[Author]: gaopeng [Blog]: blog.itpub.net/7728585/abstract/1/ [QQ]: 22389860 [Review]: yejinrong@zhishutang [Blog]: imysql.com [QQ]: 4700963 Murray- -USAGE:../innblock Datafile [scan/pageno] Blocksize [Datafile]: innodb data file! [scan]: physical scan data file to find index level and index block no [pageno]: which block you will parse [Blocksize] (KB): block size of KB general is 16k only 4k Lexus 8k Lexus 32kMurray- -scan function [root@test test] #. / innblock testblock.ibd scan 16 analyze function [root@test test] #. / innblock testblock.ibd 3 16

You can execute innblock help for more detailed usage help.

Third, innblock restrictions do not support REDUNDANT line format data files; only support LINUX x64 platform; this tool directly reads physical files, part of dirty page may delay flushing disk and can not be read, so that InnoDB can timely refresh disk and then read it; it is best to test under MySQL 5.6Comp5.7 version; only index pages can be parsed, and page;scan features such as inode page and undo log are not supported, including index blocks after delete and drop. Unable to read detailed row data;, it is recommended to use independent tablespace mode for easier observation; it is recommended to study and study only in a test environment. 4. How to use innblock

First, create a test table and populate the data

Mysql > create table testblock (id1 int primary key,name varchar (30), id3 int,key (name), key (id3)); mysql > insert into testblock values (1), (2) (2), (3) (3), (4) mysql > delete from testblock where id1=1; 1. Test scan function Scan all index page [root@test] # innblock testblock.ibd scan 16----Welcome to use this block analyze tool: [Author]: gaopeng [Blog]: blog.itpub.net/7728585/abstract/1/ [QQ]: 22389860 [Review]: yejinrong@zhishutang [Blog]: imysql.com [QQ]: 4700963----Datafile Total Size:131072===INDEX_ID:248level0 total block is (1) block_no: 3 Level: 0 | * | = = INDEX_ID:249level0 total block is (1) block_no: 4Magi level: 0 | * | = = INDEX_ID:250level0 total block is (1) block_no: 5Magi level: 0 | * |

We found that there are three indexes. The index ID (INDEX_ID) is 248,249,250 respectively. Check the data dictionary to confirm.

Mysql > SELECT A.SPACE AS TBL_SPACEID, A.TABLE_ID, A.NAME AS TABLE_NAME, FILE_FORMAT, ROW_FORMAT, SPACE_TYPE, B.INDEX_ID, B.NAME AS INDEX_NAME, PAGE_NO, B.TYPE AS INDEX_TYPE FROM INNODB_SYS_TABLES A LEFT JOIN INNODB_SYS_INDEXES B ON A.TABLE_ID = B.TABLE_ID WHERE A.NAME = 'test/testblock' + -+ | TBL_SPACEID | TABLE_ID | TABLE_NAME | FILE_FORMAT | ROW_FORMAT | SPACE_TYPE | INDEX_ID | INDEX_NAME | PAGE_NO | INDEX_TYPE | +- +-+ | 242C168 | test/testblock | Barracuda | Dynamic | Single | 248 | PRIMARY | 3 | 3 | 242C168 | test/testblock | Barracuda | Dynamic | Single | 249 | name | 4 | 0 | 242,168 | test/testblock | Barracuda | Dynamic | Single | 250 | id3 | 5 | 0 | +- -+ 2. Analyze function display

We select the index page of pageno=3 to scan, and we can see the following information

[root@test test] # innblock testblock.ibd 3 16----Welcome to use this block analyze tool: [Author]: gaopeng [Blog]: blog.itpub.net/7728585/abstract/1/ [QQ]: 22389860 [Review]: yejinrong@zhishutang [Blog]: imysql.com [QQ]: 4700963 Mustang Mustang [Block base info] = block_no:3 space_id:242 index_id:248slot_nums : 2 heaps_rows:6 n_rows:3heap_top:244 del_bytes:31 last_ins_offset:0page_dir:2 page_n_dir:3leaf_inode_space:242 leaf_inode_pag_no:2leaf_inode_offset:242no_leaf_inode_space:242 no_leaf_inode_pag_no:2no_leaf_inode_offset:50last_modify_lsn:510679871page_type: B+_TREE level:0==== Block list info =-Total used rows:5 used rows list (logic): (1) INFIMUM record offset:99 heapno:0 n_owned 1 Delflag:N minflag:0 rectype:2 (2) normal record offset:158 heapno:3 n_owned 0 deluxe minflag:0 rectype:0 N minflag:0 rectype:0 (3) normal record offset:189 heapno:4 n_owned 0Personnal deluxe minflag:0 rectype:0 N minflag:0 rectype:0 (4) normal record offset:220 heapno:5 n_owned 0Magnum delta rime N minflag:0 rectype:0 (5) SUPREMUM record offset:112 heapno:1 n_owned 4 Delflag:N minflag:0 rectype:3-Total used rows:5 used rows list (phy): (1) INFIMUM record offset:99 heapno:0 n_owned 1 minflag:0 rectype:3 N minflag:0 rectype:2 (2) SUPREMUM record offset:112 heapno:1 n_owned 4 deluxe minflag:0 rectype:3 N minflag:0 rectype:3 (3) normal record offset:158 heapno:3 n_owned 0 minflag:0 rectype:0 (4) normal record offset:189 heapno:4 n_owned 0 Delflag:N minflag:0 rectype:0 (5) normal record offset:220 heapno:5 n_owned 0 deluxe minflag:0 rectype:0-Total del rows:1 del rows list N minflag:0 rectype:0-Total del rows:1 del rows list (logic): (1) normal record offset:127 heapno:2 n_owned 0 de deluxe vision Y minflag:0 rectype:0-Total slot:2 slot list: (1) SUPREMUM slot offset:112 n_owned:4 (2) INFIMUM slot offset:99 n_owned:1 V, detailed explanation of output information

I also have a detailed explanation in the tool's help documentation, which separately explains the output of the parsing blocks of the analyze function, and I will also give which macro definition of the source code these values come from. This part of the knowledge is also described in detail in.

1. Basic information (Block base info)

[block_no]: page offset no inside space,begin is 0 (taken from FIL_PAGE_OFFSET)

Index page number (index page no), the offset of the page from the tablespace, counting from 0. If page no = 3, it is actually the fourth index page.

[space_id]: this contains the space id of the page (FIL_PAGE_SPACE_ID)

The tablespace ID to which this index page belongs can be viewed in system views such as INNODB_SYS_TABLES, INNODB_SYS_TABLESPACES, INNODB_SYS_DATAFILES, etc.

[index_id]: index id where the page belongs.This field should not be written to after page creation. (PAGE_INDEX_ID)

The index ID to which this index page belongs can be viewed in the INNODB_SYS_INDEXES system view.

[slot_nums]: number of slots in page directory (PAGE_N_DIR_SLOTS)

The number of slot (slots) contained in this index page.

[heaps_rows]: number of records in the heap include delete rows after purge and INFIMUM/SUPREMUM (taken from PAGE_N_HEAP)

The total number of records in this index page, which includes records that have been deleted and have been purged (such records will be placed in the garbage queue of the index page), as well as two pseudo records INFIMUM/SUPREMUM.

[n_rows]: number of records not include delete rows after pruge and INFIMUM/SUPREMUM (PAGE_N_RECS)

The number of records in this index page, records that do not contain deleted and have been purged, and two pseudo records INFIMUM and SUPREMUM.

[heap_top]: pointer offset to record heap top (PAGE_HEAP_TOP)

The offset that points to the maximum physical storage space allocated by this index page.

[del_bytes]: number of bytes in deleted records after purge (PAGE_GARBAGE)

The total size of all records in this index page that have been deleted and have been purged.

[last_ins_offset]: pointer to the last inserted record, or NULL if this info has been reset by a delete (PAGE_LAST_INSERT)

Point to the offset of the position where the record was last inserted in this index page. If the last action is delete, the offset is empty. It is used to judge the index splitting by judging the direction in which the data is last inserted in the index page.

[page_dir]: last insert direction: PAGE_LEFT,... (PAGE_DIRECTION)

The direction in which the data is last inserted in this index page is also used for index splitting judgment.

[page_n_dir]: number of consecutive inserts to the same direction (PAGE_N_DIRECTION)

The number of rows that insert data in the same direction, which is also used to determine in index splitting

[leaf_inode_space leaf_inode_pag_no leaf_inode_offset]: leaf segment postion and in inode block offset,only root block (PAGE_BTR_SEG_LEAF starts with 10 bytes)

[no_leaf_inode_space no_leaf_inode_pag_no no_leaf_inode_offset]: no_leaf segment postion and in inode block offset,only root block (10 bytes from PAGE_BTR_SEG_TOP)

These six values have information only in the root node, indicating the position of the inode of the leaf segment and the non-leaf segment and the offset in the inode block, respectively, and the other blocks are all 0.

[last_modify_lsn]: lsn of the end of the newest modification log record to the page (FIL_PAGE_LSN)

The LSN of the last modification of this block.

[page_type]: for this tool only B+_TREE (FIL_PAGE_TYPE)

It is always B + TREE for this tool because other page type is not supported.

[level]: level of the node in an index tree; the leaf level is the level 0 (PAGE_LEVEL)

The level of B+ TREE in which this index page is located. Notice that the PAGE LEVEL of the leaf node is 0.

2. Four related linked lists (Block list info)

Total used rows:5 used rows list (logic):

Not delete purge rows and not delete logic sequence list (next offset list).

This linked list is a logically ordered linked list, and it is also what we usually call an orderly display of intra-block data. Its order is, of course, arranged by primary key or ROWID, because it is achieved through a physical offset linked list, which is actually logically ordered. I actually took the offset of INFIMUM to scan until the end, but notice that the records that have been deleted and have been purged are not among them.

Total used rows:5 used rows list (phy):

Not delete purge rows and not delete physics sequence list (sort by heap no).

This linked list is a physical order, which is actually the order of heap no. When I implemented it, I actually sorted the above logical linked list according to heap no, so the interior of the block is logically ordered and physically disordered. Also note that the records that have been deleted and have been purged are not among them.

Total del rows:1 del rows list (logic):

Purge delete logic sequence list (next offset list).

The linked list is logical, that is, all the records that are deleted and purged exist in the linked list, and the linked list information is obtained by reading the PAGE_FREE of the block.

Total slot:2 slot list:

Slot physics sequence list.

This is the slot (slot) information, which is obtained by scanning the information before 8 bytes at the end of the block, and we can find that what is stored in the slot is the recorded offset.

Here the linked list contains some information, which is given here using the parsing in help.

[record offset]: real offset in block of this record. [heapno]: physics heapno of this record.[n _ owned]: if this record is slot record n_owned is how many this slot include,other is 0. [delflag]: this record is delete will Y,if not purge in list 1,if purge in list 3. [rectype]:

[REC_STATUS_ORDINARY=0 (B + leaf record)

[REC_STATUS_NODE_PTR=1 (not B + leaf record)]

[REC_STATUS_INFIMUM=2]

[REC_STATUS_SUPREMUM=3] [slot offset]: where (offset) this slot point,this is a record offset.no purge delete record. [n _ owned]: how many this slot include recorods.no purge delete record. 6. Several test cases

The full usage test tables in this section are as follows:

Mysql > create table testblock (id1 int primary key,name varchar (30), id3 int,key (name), key (id3))

Initialize the test data:

Mysql > insert into testblock values (1), (2) (2), (3), (4), (4); 1. The records that have not been commit after the execution of delete are marked only with delete.

To initiate a transaction, execute delete first. Do not commit for the time being

Mysql > begin; delete from testblock where id1=1

Analysis results:

[root@test] # innblock testblock.ibd 3 16 block_no:3 space_id:242 index_id:248slot_nums:2 heaps_rows:6 n_rows:4heap_top:244 del_bytes:0 last_ins_offset:220page_dir:2 page_n_dir:3leaf_inode_space:242 leaf_inode_pag_no:2leaf_inode_ = Block base info = = Offset:242no_leaf_inode_space:242 no_leaf_inode_pag_no:2no_leaf_inode_offset:50last_modify_lsn:510695376page_type:B+_TREE level:0==== Block list info =-Total used rows:6 used rows list (logic): (1) INFIMUM record offset:99 heapno:0 n_owned 1 Delflag:N minflag:0 rectype:2 (2) normal record offset:127 heapno:2 n_owned 0Magnum deluxe minflag:0 rectype:0 Y minflag:0 rectype:0 (3) normal record offset:158 heapno:3 n_owned 0Personnal deluxe minflag:0 rectype:0 N minflag:0 rectype:0 (4) normal record offset:189 heapno:4 n_owned 0Magnum deluxe N minflag:0 rectype:0 (5) normal record offset:220 heapno:5 n_owned 0Magnum delta SUPREMUM record offset:112 heapno:1 n_owned N minflag:0 rectype:0 (6) SUPREMUM record offset:112 heapno:1 n_owned 5 Delflag:N minflag:0 rectype:3-Total used rows:6 used rows list (phy): (1) INFIMUM record offset:99 heapno:0 n_owned 1 Deluxe minflag:0 rectype:2 N minflag:0 rectype:2 (2) SUPREMUM record offset:112 heapno:1 n_owned 5 Delphi Velcro N minflag:0 rectype:3 (3) normal record offset:127 heapno:2 n_owned 0 Delphi Y minflag:0 rectype:0 (4) normal record offset:158 heapno:3 n_owned 0 Delflag:N minflag:0 rectype:0 (5) normal record offset:189 heapno:4 n_owned 0 deluxe minflag:0 rectype:0 N minflag:0 rectype:0 (6) normal record offset:220 heapno:5 n_owned 0 recorder delta minflag:0 rectype:0 N minflag:0 rectype:0-Total del rows:0 del rows list (logic):-Total slot:2 slot list: (1) SUPREMUM slot offset:112 n_owned:5 (2) INFIMUM slot offset:99 n_owned:1

We saw that one of the records was

(2) normal record offset:127 heapno:2 n_owned 0delight minflag:0 rectype:0

This record is only delete, but it has not been commit and has not been purged, so it will not appear in the del rows list linked list.

Notice several messages at the same time:

Del_bytes:0n_rows:4heaps_rows:6

Taken together, the three pieces of information indicate that the data has not really been purged.

2. The record of commit after the execution of delete is really cleared by purged and goes into the deleted linked list.

Proceed with the above transaction and continue to execute commit

Mysql > commit;Query OK, 0 rows affected (0.00 sec)

Analysis results:

= Block base info = block_no:3 space_id:242 index_id:248slot_nums:2 heaps_rows:6 n_rows:3heap_top:244 del_bytes:31 last_ins_offset:0page_dir:2 page_n_dir:3leaf_inode_space:242 leaf_inode_pag_no:2leaf_inode_offset:242no_leaf_inode_space:242 No_leaf_inode_pag_no:2no_leaf_inode_offset:50last_modify_lsn:510695802page_type:B+_TREE level:0==== Block list info =-Total used rows:5 used rows list (logic): (1) INFIMUM record offset:99 heapno:0 n_owned 1 Delflag:N minflag:0 rectype:2 (2) normal record offset:158 heapno:3 n_owned 0 deluxe minflag:0 rectype:0 N minflag:0 rectype:0 (3) normal record offset:189 heapno:4 n_owned 0Personnal deluxe minflag:0 rectype:0 N minflag:0 rectype:0 (4) normal record offset:220 heapno:5 n_owned 0Magnum delta rime N minflag:0 rectype:0 (5) SUPREMUM record offset:112 heapno:1 n_owned 4 Delflag:N minflag:0 rectype:3-Total used rows:5 used rows list (phy): (1) INFIMUM record offset:99 heapno:0 n_owned 1 minflag:0 rectype:3 N minflag:0 rectype:2 (2) SUPREMUM record offset:112 heapno:1 n_owned 4 deluxe minflag:0 rectype:3 N minflag:0 rectype:3 (3) normal record offset:158 heapno:3 n_owned 0 minflag:0 rectype:0 (4) normal record offset:189 heapno:4 n_owned 0 Delflag:N minflag:0 rectype:0 (5) normal record offset:220 heapno:5 n_owned 0 deluxe minflag:0 rectype:0-Total del rows:1 del rows list N minflag:0 rectype:0-Total del rows:1 del rows list (logic): (1) normal record offset:127 heapno:2 n_owned 0 de deluxe rig Y minflag:0 rectype:0-Total slot:2 slot list: (1) SUPREMUM slot offset:112 n_owned:4 (2) INFIMUM slot offset:99 n_owned:1

We can see that when commit is executed, the record with an offset of 127is added to the del rows list linked list after being purged.

(1) normal record offset:127 heapno:2 n_owned 0 delta deluxe minflag:0 rectype:0

Its delflag = Y, at the same time we observe

Del_bytes:31 / / the last value seen is 0n_rows:3 / / the last seen value is the same as the last one, because the number of physical records is calculated here

It can be seen that the data that is commit and purged is really deleted (erased).

3. Delete and insert larger new records first, and the old heap no will not be reused.

The heapno of the deleted record above is 2, and then insert the new record

Insert into testblock values (5 minutes gaopengli 1)

It is obviously longer than the length of the deleted record.

Analysis results:

= Block base info = block_no:3 space_id:242 index_id:248slot_nums:2 heaps_rows:7 n_rows:4heap_top:279 del_bytes:31 last_ins_offset:251page_dir:5 page_n_dir:0leaf_inode_space:242 leaf_inode_pag_no:2leaf_inode_offset:242no_leaf_inode_space:242 No_leaf_inode_pag_no:2no_leaf_inode_offset:50last_modify_lsn:510695994page_type:B+_TREE level:0==== Block list info =-Total used rows:6 used rows list (logic): (1) INFIMUM record offset:99 heapno:0 n_owned 1 Delflag:N minflag:0 rectype:2 (2) normal record offset:158 heapno:3 n_owned 0 deluxe minflag:0 rectype:0 N minflag:0 rectype:0 (3) normal record offset:189 heapno:4 n_owned 0Personnal deluxe minflag:0 rectype:0 N minflag:0 rectype:0 (4) normal record offset:220 heapno:5 n_owned 0minddeliciouslace N minflag:0 rectype:0 (5) normal record offset:251 heapno:6 n_owned 0deliciouslace N minflag:0 rectype:0 (6) SUPREMUM record offset:112 heapno:1 n_owned 5 Delflag:N minflag:0 rectype:3-Total used rows:6 used rows list (phy): (1) INFIMUM record offset:99 heapno:0 n_owned 1 deluxe minflag:0 rectype:2 N minflag:0 rectype:2 (2) SUPREMUM record offset:112 heapno:1 n_owned 5 Delphi Velcro N minflag:0 rectype:3 (3) normal record offset:158 heapno:3 n_owned 0 Delphi N minflag:0 rectype:0 (4) normal record offset:189 heapno:4 n_owned 0 Delflag:N minflag:0 rectype:0 (5) normal record offset:220 heapno:5 n_owned 0 deluxe minflag:0 rectype:0 N minflag:0 rectype:0 (6) normal record offset:251 heapno:6 n_owned 0 recorder delta rime N minflag:0 rectype:0-Total del rows:1 del rows list (logic): (1) normal record offset:127 heapno:2 n_owned 0 Delflag:Y minflag:0 rectype:0-Total slot:2 slot list: (1) SUPREMUM slot offset:112 n_owned:5 (2) INFIMUM slot offset:99 n_owned:1

We saw a new record.

(5) normal record offset:251 heapno:6 n_owned 0mm delta minflag:0 rectype:0 N

The heapno= 6 of this record, while the deleted old record heapno=2, indicates that it does not reuse the space in the del rows list, because the space for deleting the record simply cannot hold the new record, so it can only be reallocated. At the same time, we notice that * * heap_top = 279 * *, which has also changed, reflecting the fact that a new heapno is actually allocated to this row of data.

4. After delete, heap no will reuse insert records of smaller size or the same size.

On the basis of the above, we insert new records

Insert into testblock values (6 recordings gaoying 1)

Analysis results:

= Block base info = block_no:3 space_id:242 index_id:248slot_nums:2 heaps_rows:7 n_rows:5heap_top:279 del_bytes:0 last_ins_offset:127page_dir:2 page_n_dir:1leaf_inode_space:242 leaf_inode_pag_no:2leaf_inode_offset:242no_leaf_inode_space:242 No_leaf_inode_pag_no:2no_leaf_inode_offset:50last_modify_lsn:510700272page_type:B+_TREE level:0==== Block list info =-Total used rows:7 used rows list (logic): (1) INFIMUM record offset:99 heapno:0 n_owned 1 Delflag:N minflag:0 rectype:2 (2) normal record offset:158 heapno:3 n_owned 0Magnum deluxe minflag:0 rectype:0 N minflag:0 rectype:0 (3) normal record offset:189 heapno:4 n_owned 0Personnal deluxe minflag:0 rectype:0 N minflag:0 rectype:0 (4) normal record offset:220 heapno:5 n_owned 0Different deluxe minflag:0 rectype:0 N minflag:0 rectype:0 (5) normal record offset:251 heapno:6 n_owned 0Magnum deliciosa N minflag:0 rectype:0 (6) normal record offset:127 heapno:2 n_owned 0 Delflag:N minflag:0 rectype:0 (7) SUPREMUM record offset:112 heapno:1 n_owned 6 deluxe minflag:0 rectype:3-Total used rows:7 used rows list N minflag:0 rectype:3-Total used rows:7 used rows list (phy): (1) INFIMUM record offset:99 heapno:0 n_owned 1 Magnum deluxe minflag:0 rectype:3-Total used rows:7 used rows list N minflag:0 rectype:2 (2) SUPREMUM record offset:112 heapno:1 n_owned 6 Delphi Vega N minflag:0 rectype:3 (3) normal record offset:127 heapno:2 n_owned 0 Delflag:N minflag:0 rectype:0 (4) normal record offset:158 heapno:3 n_owned 0 deluxe minflag:0 rectype:0 N minflag:0 rectype:0 (5) normal record offset:189 heapno:4 n_owned 0pm delta minflag:0 rectype:0 N minflag:0 rectype:0 (6) normal record offset:220 heapno:5 n_owned 0th delta laze N minflag:0 rectype:0 (7) normal record offset:251 heapno:6 n_owned 0 Delflag:N minflag:0 rectype:0-Total del rows:0 del rows list (logic):-Total slot:2 slot list: (1) SUPREMUM slot offset:112 n_owned:6 (2) INFIMUM slot offset:99 n_owned:1

The length of the newly written data is the same as that of the deleted data this time. We find that heapno reuses the records in del rows list, but adds one more item in the logical order of the data.

(6) normal record offset:127 heapno:2 n_owned 0mm delta minflag:0 rectype:0 N

We found that the record delflag of heapno=2 is no longer Y, and heap_top = 279 * * has not changed, and del_bytes:31** has become del_bytes:0, which fully illustrates the reuse of this space.

5. Testing the spatial reuse in del list will only detect the record deleted by the first bar.

Perform the test after emptying the data table

Mysql > insert into testblock values (1), (2) (2), (3), (4), (4) mysql > delete from testblock where id1=4;mysql > delete from testblock where id1=3;mysql > insert into testblock values (5)

Here, we first delete the [id1=4] record, and then delete the [id1=3] record.

Because del list is head insertion, the record of the later deleted [id1=3] is placed at the top of the del list linked list, that is, [del list header] = > [id1=3] = > [id1=4]. Although [id=4] has enough record space for new records, it has not been reused. Because InnoDB only detects the first vacancy in the first del list [id1=3], it is obvious that this record space is not enough for new records (5), so a new heap has been opened up.

Analysis results:

= Block base info = block_no:3 space_id:242 index_id:248slot_nums:2 heaps_rows:7 n_rows:3heap_top:283 del_bytes:66 last_ins_offset:255page_dir:5 page_n_dir:0leaf_inode_space:242 leaf_inode_pag_no:2leaf_inode_offset:242no_leaf_inode_space:242 No_leaf_inode_pag_no:2no_leaf_inode_offset:50last_modify_lsn:510728551page_type:B+_TREE level:0==== Block list info =-Total used rows:5 used rows list (logic): (1) INFIMUM record offset:99 heapno:0 n_owned 1 Delflag:N minflag:0 rectype:2 (2) normal record offset:127 heapno:2 n_owned 0 deluxe minflag:0 rectype:0 N minflag:0 rectype:0 (3) normal record offset:158 heapno:3 n_owned 0Personnal deluxe minflag:0 rectype:0 N minflag:0 rectype:0 (4) normal record offset:255 heapno:6 n_owned 0Magnum delta rime N minflag:0 rectype:0 (5) SUPREMUM record offset:112 heapno:1 n_owned 4 Delflag:N minflag:0 rectype:3-Total used rows:5 used rows list (phy): (1) INFIMUM record offset:99 heapno:0 n_owned 1 minflag:0 rectype:3 N minflag:0 rectype:2 (2) SUPREMUM record offset:112 heapno:1 n_owned 4 deluxe minflag:0 rectype:3 N minflag:0 rectype:3 (3) normal record offset:127 heapno:2 n_owned 0 minflag:0 rectype:0 (4) normal record offset:158 heapno:3 n_owned 0 Delflag:N minflag:0 rectype:0 (5) normal record offset:255 heapno:6 n_owned 0Magnum deluxe minflag:0 rectype:0-Total del rows:2 del rows list N minflag:0 rectype:0-Total del rows:2 del rows list (logic): (1) normal record offset:189 heapno:4 n_owned 0Magnee delta Vega Y minflag:0 rectype:0 (2) normal record offset:220 heapno:5 n_owned 0 Delflag:Y minflag:0 rectype:0-Total slot:2 slot list: (1) SUPREMUM slot offset:112 n_owned:4 (2) INFIMUM slot offset:99 n_owned:1

We see that del list has a total of 2 records (not reused), but a new record with heapno = 6 has been added.

6. Whether del_bytes (PAGE_GARBAGE) contains debris space

From the point of view of the reassembly function btr_page_reorganize_low, PAGE_GARBAGE does contain fragment space.

Perform the test after emptying the data table

Mysql > insert into testblock values (1), (2) (2), (3), (3), (4), (4) mysql > delete from testblock where id1=4

Analysis results:

= Block base info = block_no:3 space_id:242 index_id:248slot_nums:2 heaps_rows:6 n_rows:3heap_top:248 del_bytes:35 last_ins_offset:0page_dir:2 page_n_dir:3leaf_inode_space:242 leaf_inode_pag_no:2leaf_inode_offset:242no_leaf_inode_space:242 No_leaf_inode_pag_no:2no_leaf_inode_offset:50last_modify_lsn:510748484page_type:B+_TREE level:0==== Block list info =-Total used rows:5 used rows list (logic): (1) INFIMUM record offset:99 heapno:0 n_owned 1 Delflag:N minflag:0 rectype:2 (2) normal record offset:127 heapno:2 n_owned 0 deluxe minflag:0 rectype:0 N minflag:0 rectype:0 (3) normal record offset:158 heapno:3 n_owned 0Personnal deluxe minflag:0 rectype:0 N minflag:0 rectype:0 (4) normal record offset:189 heapno:4 n_owned 0Magnum delta rime N minflag:0 rectype:0 (5) SUPREMUM record offset:112 heapno:1 n_owned 4 Delflag:N minflag:0 rectype:3-Total used rows:5 used rows list (phy): (1) INFIMUM record offset:99 heapno:0 n_owned 1 minflag:0 rectype:3 N minflag:0 rectype:2 (2) SUPREMUM record offset:112 heapno:1 n_owned 4 deluxe minflag:0 rectype:3 N minflag:0 rectype:3 (3) normal record offset:127 heapno:2 n_owned 0 minflag:0 rectype:0 (4) normal record offset:158 heapno:3 n_owned 0 Delflag:N minflag:0 rectype:0 (5) normal record offset:189 heapno:4 n_owned 0 deluxe minflag:0 rectype:0-Total del rows:1 del rows list N minflag:0 rectype:0-Total del rows:1 del rows list (logic): (1) normal record offset:220 heapno:5 n_owned 0 de deluxe rig Y minflag:0 rectype:0-Total slot:2 slot list: (1) SUPREMUM slot offset:112 n_owned:4 (2) INFIMUM slot offset:99 n_owned:1

Note that del_bytes:35 is the amount of space used to delete this record. Next, execute SQL.

Mysql > insert into testblock values (5)

Re-analyze the results:

= Block base info = block_no:3 space_id:242 index_id:248slot_nums:2 heaps_rows:6 n_rows:4heap_top:248 del_bytes:4 last_ins_offset:220page_dir:5 page_n_dir:0leaf_inode_space:242 leaf_inode_pag_no:2leaf_inode_offset:242no_leaf_inode_space:242 No_leaf_inode_pag_no:2no_leaf_inode_offset:50last_modify_lsn:510748643page_type:B+_TREE level:0==== Block list info =-Total used rows:6 used rows list (logic): (1) INFIMUM record offset:99 heapno:0 n_owned 1 Delflag:N minflag:0 rectype:2 (2) normal record offset:127 heapno:2 n_owned 0 deluxe minflag:0 rectype:0 N minflag:0 rectype:0 (3) normal record offset:158 heapno:3 n_owned 0Personnal deluxe minflag:0 rectype:0 N minflag:0 rectype:0 (4) normal record offset:189 heapno:4 n_owned 0minddeliciouslace N minflag:0 rectype:0 (5) normal record offset:220 heapno:5 n_owned 0deliciouslace N minflag:0 rectype:0 (6) SUPREMUM record offset:112 heapno:1 n_owned 5 Delflag:N minflag:0 rectype:3-Total used rows:6 used rows list (phy): (1) INFIMUM record offset:99 heapno:0 n_owned 1 deluxe minflag:0 rectype:2 N minflag:0 rectype:2 (2) SUPREMUM record offset:112 heapno:1 n_owned 5 Delphi Velcro N minflag:0 rectype:3 (3) normal record offset:127 heapno:2 n_owned 0 Delphi N minflag:0 rectype:0 (4) normal record offset:158 heapno:3 n_owned 0 Delflag:N minflag:0 rectype:0 (5) normal record offset:189 heapno:4 n_owned 0 deluxe minflag:0 rectype:0 N minflag:0 rectype:0 (6) normal record offset:220 heapno:5 n_owned 0 recorder delta minflag:0 rectype:0 N minflag:0 rectype:0-Total del rows:0 del rows list (logic):-Total slot:2 slot list: (1) SUPREMUM slot offset:112 n_owned:5 (2) INFIMUM slot offset:99 n_owned:1

Notice del_bytes:4, which happens to be the remaining 4 bytes of 'gaopeng' 7 bytes minus' gao' 3 bytes, and we also see that [heapno=5] this record is reused (del list is empty, heaono=5 's record delflag is not Y).

In short, this tool can carry out all kinds of tests and observations according to your ideas.

Memory leak detection

In fact, I did not show the memory allocation in this tool. The memory allocation basically uses the STL LIST container detection results as follows:

= 11984 blocks==11984== suppressed = LEAK SUMMARY:==11984== definitely lost: 0 bytes in 0 blocks==11984== indirectly lost: 0 bytes in 0 blocks==11984== possibly lost: 0 bytes in 0 blocks==11984== still reachable: 568 bytes in 1 blocks==11984== suppressed: 0 bytes in 0 blocks==11984== Reachable blocks (those to which a pointer was found) are not shown.==11984== To see them, rerun with:-- leak-check=full-- show-reachable=yes 8, Summary

This tool basically collects all the fixed information of the InnoDB index page, hoping to help you get all kinds of information more conveniently, and the efficiency is obviously higher than that of looking at binary files with the naked eye, which is not only the author's dilemma in analyzing InnoDB, but also the starting point of writing this gadget.

Finally, I would like to thank Ye Jinrong again for his review and suggestion of the tool. At the same time, I would like to thank Zhou Yanwei, Wang Zhufeng and Qiang Changjin, the three authors of MySQL operation and maintenance staff, for their recognition of this tool, which is also my greatest honor.

Author Wechat

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.