In-depth Analysis of InnoDB File Structure and Page Organization

InnoDB files consist of uniformly structured pages (16 KB by default) that contain a file header, page header, infimum and supremum records, user records, free space, a page directory, and a file trailer. The article explains each component and shows how to interpret them using hexdump output.

File Header (Fil Header) – 38 bytes storing checksum, page number, space ID, LSN, and other metadata. Example hexdump:

$hexdump -s 0xc000 -n 38 -C t.ibd
0000c000  63 ad 74 69 00 00 00 03 ff ff ff ff ff ff ff ff  |c.ti............|
0000c010  00 00 00 00 a8 cf e0 a4 45 bf 00 00 00 00 00 00  |........E.......|
0000c020  00 00 00 00 00 55                                 |.....U|
0000c026

The page number (00 00 00 03) identifies the root page, and the type 0x45bf indicates an index page.

Page Header – 56 bytes divided into 14 fields (e.g., PAGE_N_DIR_SLOTS, PAGE_N_HEAP, PAGE_LEVEL). Example:

$hexdump -s 0xc026 -n 56 -C t.ibd
0000c026  00 02 00 d8 80 05 00 a0 00 20 00 00 00 02 00 02  |......... ......|
0000c036  00 02 00 00 00 00 00 00 00 00 00 00 00 00 00 00  |................|
0000c046  00 00 00 68 00 00 00 55 00 00 00 02 00 f2 00 00  |...h...U........|
0000c056  00 55 00 00 00 02 00 32                           |.U.....2|
0000c05e

Key values such as PAGE_N_DIR_SLOTS=2 indicate two slots (infimum and supremum), PAGE_N_HEAP=0x80 05 shows a dynamic row format, and PAGE_LEVEL=0 marks a leaf page.

Record Storage Format (Dynamic Row) – Each user record begins with a variable‑length field list, a NULL bitmap, a fixed 5‑byte record header, and then the actual column data. Example record dump:

$hexdump -s 0xc078 -n 26 -C t.ibd
0000c078  06 01 00 00 00 10 00 40 80 00 00 01 00 00 00 00  |.......@........|
0000c088  61 39 b5 00 00 01 2a 01 10 61 31 31 31 61 61 61  |a9....*..a111aaa|
0000c098

The variable‑length list 06 01 indicates c1 length 1 and c2 length 6. The NULL bitmap 00 shows no NULL columns. The record header 00 00 10 00 40 contains heap number and type. The data section reveals the primary key (id = 1), transaction ID, rollback pointer, and column values ('a' and '111aaa').

Infimum and Supremum Records – System records without variable‑length or NULL lists. Example:

$hexdump -s 0xc05e -n 26 -C t.ibd
0000c05e  01 00 02 00 1d 69 6e 66 69 6d 75 6d 00 03 00 0b  |.....infimum....|
0000c06e  00 00 73 75 70 72 65 6d 75 6d                    |..supremum|
0000c078

These records store the strings "infimum" and "supremum" and serve as page boundaries.

Free Space – The area between user records and the page directory that holds unused bytes. When space runs low, InnoDB compacts the page and updates PAGE_N_RECS and PAGE_N_HEAP.

Page Directory – Acts as an internal index of slots, each 2 bytes pointing to a record offset. Example slot dump:

$hexdump -s 0xfff4 -n 4 -C t.ibd
0000fff4  00 70 00 63  |.p.c|
0000fff8

Slots point to the infimum (offset 0x70) and supremum (offset 0x63) records.

File Trailer (Fil Trailer) – 8 bytes storing checksum and the last four bytes of the LSN, mirroring the header values.

External Storage Pages (Blob Pages) – When a variable‑length column exceeds the page limit, its data is stored on a separate blob page, and the index page holds a 20‑byte pointer. Example pointer extraction shows space ID 0x55, page 4, offset 0x26, and total length 0xc350 (50 000 bytes).

Row Formats Overview – InnoDB supports Redundant, Compact, Dynamic, and Compressed formats. The article compares them, noting that Redundant lacks a NULL bitmap, Compact saves ~20 % space versus Redundant, and Compressed adds page‑level compression with optional KEY_BLOCK_SIZE.

Compression Techniques – Transparent page compression uses Linux punch‑hole to store only the header and compressed data, while compressed tables store data in an MLOG area and recompress when the log fills. Example DDL statements for each method are provided.

Overall, the article demonstrates how to dissect InnoDB pages, interpret their binary structures, and understand the impact of different row formats and compression options on storage and performance.