Moment For Technology

Explore InnoDB page management using Innodb_Ruby

Posted on Dec. 2, 2022, 6:41 p.m. by Debra Macias
Category: The back-end Tag: The back-end mysql

This series of articles is a translation of the InnoDB series from Jeremy Cole's Blog. This paper is the fifth of 16 articles. Exploring InnoDB Page Management with Innodb_Ruby

Due to the limited amount of translation, in order to avoid misunderstanding to the reader, some proper nouns will be followed by [] marking the original text.

Explore InnoDB page management using Innodb_Ruby

[Innodb_Ruby version 0.8.8 used in this article (released February 3, 2014)]

In On Learning InnoDB: A Journey to the Core, I mentioned the Innodb_Ruby project and its command-line tools. In A follow-up to A Quick Introduction to Innodb_Ruby, I covered the installation of the Innodb_space command-line tool and some quick demonstrations.

In my last article Page Management in InnoDB Space Files, I described InnoDB's structure related to exten, file segments, and free space management. Next, I'll provide some examples of using Innodb_space to examine these structures in real tables.

A minimal empty table

I created an empty table (table structure is not important) to illustrate the "minimal" state of InnoDB's page management structure. The space-page-type-regions command summarizes all continuous regions on a page of the same type:

$ innodb_space -f test/e.ibd space-page-type-regions
start       end         count       type                
0           0           1           FSP_HDR             
1           1           1           IBUF_BITMAP         
2           2           1           INODE               
3           3           1           INDEX               
4           5           2           FREE (ALLOCATED)  
Copy the code

As you can see from the output, this table has ALLOCATED 'standard' pages in the.idb file: FSP_HDR page, IBUF_BITMAP page, INODE page, INDEX page (root for empty INDEX), and two unused FREE pages.

The space-lists command summarizes the list of extent descriptors and inodes in the space:

$ innodb_space -f test/e.ibd space-lists
name                length      f_page      f_offset    l_page      l_offset    
free                0           0           0           0           0           
free_frag           1           0           158         0           158         
full_frag           0           0           0           0           0           
full_inodes         0           0           0           0           0           
free_inodes         1           2           38          2           38          
Copy the code

Only the free_FRAg extent descriptor list has entries, and there is only one extent in it. There is an INODE page in the free_inodes list.

You can check the contents of the Free_frag linked list with the space-list-iterate command, which prints a graph showing the use of all the pages in the extent list ("#" indicates that the page is used, and "." indicates that the page is free) :

$ innodb_space -f test/e.ibd -L free_frag space-list-iterate start_page page_used_bitmap 0 # # # #...Copy the code

As you can see from the space-page type-regions command output above, InnoDB is currently allocating only 6 pages. The output of space-list-Iterate should not have 64 # or... in the first section. If you run the above command with the latest version of Innodb_Ruby, it will only print ####.. , indicating that four pages are in use and two pages are not in use.

Space-indexes aggregates segments of all indexes in the space:

$innodb_space -f test/ e.bd space-Indexes ID root fseg used allocated fill_factor 16 3 internal 1 1 100.00% 16 3 leaf 0 0 0.00%Copy the code

Only "internal" file segments are allocated pages, and only one page is allocated. The index-fseg-internal-lists mode will summarize the extentlists in "internal" file segments:

$ innodb_space -f test/e.ibd -p 3 index-fseg-internal-lists
name                length      f_page      f_offset    l_page      l_offset    
free                0           0           0           0           0           
not_full            0           0           0           0           0           
full                0           0           0           0           0           
Copy the code

All three lists are empty because the empty table has not allocated any full extents. So, where is a page that has been used? This is a "shard" page that can be listed using the index-fseg-internal-frag-pages mode:

$ innodb_space -f test/e.ibd -p 3 index-fseg-internal-frag-pages
page        index   level   data    free    records 
3           16      0       0       16252   0       
Copy the code

This is the "minimized" state of the table -- a space file with a mostly blank bookeeping structure and only an INDEX page. Let's look at a table that contains some actual data.

A table with a million rows

In A Quick Introduction to Innodb_Ruby, I created A table with 1 million rows. We will use the same table in the following example.

Using the space-page type-regions command, you can see that there are a total of 2,165 pages of type INDEX:

$ innodb_space -f test/t.ibd space-page-type-regions start end count type 3 37 35 INDEX 38 63 26 FREE (ALLOCATED) 64 2188 2125 INDEX 2189 2239 51 FREE (ALLOCATED) 2240 2240 1 INDEX 2241 2303 63 FREE (ALLOCATED) 2304 2304 1 INDEX 2305 2367 63 FREE (ALLOCATED) 2368 2368 1 INDEX 2369 2431 63 FREE (ALLOCATED) 2432 2432 1 INDEX 2433 2495 63 FREE (ALLOCATED)  2496 2496 1 INDEX 2497 2687 191 FREE (ALLOCATED)Copy the code

Note: The above output only shows INDEX and FREE pages. If you actually use this command you should also display other pages such as FSP_HDR pages, IBUF_BITMAP pages, and INODE pages.

Note that in the middle of the contiguous area of the INDEX page there is a gap of FREE (ALLOCATED) pages. This is because InnoDB does not guarantee that free pages will be used sequentially, and many optimizations about bulk data loading also cause pages to be used out of order. (More on page splitting and these optimizations in a future article)

Looking at the linked list in space, there are some extences in the free list and some extences in the free_FRAg list:

$ innodb_space -f test/t.ibd space-lists
name                length      f_page      f_offset    l_page      l_offset    
free                2           0           1758        0           1798        
free_frag           1           0           158         0           158         
full_frag           0           0           0           0           0           
full_inodes         0           0           0           0           0           
free_inodes         1           2           38          2           38          
Copy the code

As expected, all pages of the extent in the free extent descriptor list are free:

$ innodb_space -f test/t.ibd -L free space-list-iterate 2560 . 2624...Copy the code

The free_FRAg extent descriptor linked list has some "fragmented" pages that are used:

$ innodb_space -f test/t.ibd -L free_frag space-list-iterate start_page page_used_bitmap 0 # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #...Copy the code

As you can see from the space-indexes command, a large number of used pages are allocated to leaf file segments. As expected, there are only three "internal" pages to manage the 2,162 "leaf" pages in the B + tree:

$innodb_space -f test/ t.I bd space-Indexes ID root fseg used allocated fill_factor 15 3 internal 3 3 100.00% 15 3 leaf 85.52% of the 2162, 2528Copy the code

You can also see that the leaf segments allocate more pages than they actually use, with the fill_factor (" fill factor ") of 85.52%. This is because InnoDB's segment fill factor is fixed at 87.5% in MySQL. It is now possible to configure it in Twitter MySQL, thanks to Facebook submitting MySQL Bug 64673.

Because the internal file segment has only three pages, the file segment list is empty:

$ innodb_space -f test/t.ibd -p 3 index-fseg-internal-lists
name                length      f_page      f_offset    l_page      l_offset    
free                0           0           0           0           0           
not_full            0           0           0           0           0           
full                0           0           0           0           0           
Copy the code

Three used pages are allocated as fragmented pages:

$ innodb_space -f test/t.ibd -p 3 index-fseg-internal-frag-pages
page        index   level   data    free    records 
3           15      2       26      16226   2       
36          15      1       14521   1401    1117    
37          15      1       13585   2341    1045    
Copy the code

On the other hand, the linked list of leaf index file segments is "busy" with 33 full extentsand 6 not_full extents.Txt:

$ innodb_space -f test/t.ibd -p 3 index-fseg-leaf-lists
name                length      f_page      f_offset    l_page      l_offset    
free                0           0           0           0           0           
not_full            6           0           1518        0           1718        
full                33          0           198         0           1478        
Copy the code

In addition, the leaf file segment has allocated all 32 shard pages that can be allocated (shard pages are allocated before full extones are allocated) :

$ innodb_space -f test/t.ibd -p 3 index-fseg-leaf-frag-pages
page        index   level   data    free    records 
4           15      0       9812    6286    446     
5           15      0       15158   860     689     
6           15      0       10912   5170    496     
7           15      0       10670   5412    485     
8           15      0       12980   3066    590     
9           15      0       11264   4808    512     
10          15      0       4488    11690   204     
11          15      0       9680    6418    440     
12          15      0       9306    6800    423     
13          15      0       9658    6434    439     
14          15      0       10032   6062    456     
15          15      0       9988    6108    454     
16          15      0       9570    6530    435     
17          15      0       9130    6978    415     
18          15      0       8844    7266    402     
19          15      0       11770   4300    535     
20          15      0       9020    7092    410     
21          15      0       8646    7462    393     
22          15      0       9746    6354    443     
23          15      0       11066   5014    503     
24          15      0       8910    7204    405     
25          15      0       11748   4322    534     
26          15      0       10978   5094    499     
27          15      0       11132   4940    506     
28          15      0       9350    6750    425     
29          15      0       13508   2526    614     
30          15      0       14938   1082    679     
31          15      0       14520   1506    660     
32          15      0       9086    7016    413     
33          15      0       9724    6368    442     
34          15      0       10978   5102    499     
35          15      0       9504    6592    432     
Copy the code

As expected, the full section is "full" :

$ innodb_space -f test/t.ibd -p 3 -L full index-fseg-leaf-list-iterate start_page page_used_bitmap 64 # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 128 # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 192 # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 256 # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 320 # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 384 # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 448 # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 512 # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 576 # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 640 # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 704 # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 768 # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 832 # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 896 # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 960 # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 1024 # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 1088 # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 1152 # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 1216 # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 1280 # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 1344 # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 1408 # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 1472 # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 1536 # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 1600 # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 1664 # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 1728 # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 1792 # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 1856 # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 1920 # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 1984 # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 2048 # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 2112 # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #Copy the code

The not_full section is all partially free, as expected:

$ innodb_space -f test/t.ibd -p 3 -L not_full index-fseg-leaf-list-iterate start_page page_used_bitmap 2176 # # # # # # # # # # # # #... 2240 #... 2304 #... 2368 #... 2432 #... 2496 #...Copy the code

You can see a little shadow of InnoDB's page splitting optimization here: In order to order data on disk, InnoDB allocates pages from the first page in the free section many times (page numbers are the most suspicious thing). We will study this behavior in more depth in the future.

Translator's note: The "suspicious thing" here refers to the fact that the start_pages of the above not_full extent lists are all at the same interval 64.

Search
About
mo4tech.com (Moment For Technology) is a global community with thousands techies from across the global hang out!Passionate technologists, be it gadget freaks, tech enthusiasts, coders, technopreneurs, or CIOs, you would find them all here.