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 codeAs 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 codeOnly 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 codeAs 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 codeOnly “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 codeAll 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 codeThis 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 codeNote: 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 codeAs 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 codeThe 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 codeAs 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 codeYou 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 codeThree 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 codeOn 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 codeIn 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 codeAs 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 codeThe 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 codeYou 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.