Understanding InnoDB Tablespace Space Management

In InnoDB, each user‑defined table and its indexes are stored in files with the .ibd extension. Two kinds of tablespaces exist: a shared (general) tablespace where many tables can share a single .ibd file, and a file‑per‑table tablespace where each table has its own .ibd file.

Creating a simple table demonstrates the file‑per‑table layout:

mysql> CREATE TABLE test.t1 (c INT) ENGINE=InnoDB;

$ cd /test

$ ls

After the command, a t1.ibd file appears, containing the data and index pages for t1 .

For a file‑per‑table tablespace, the tablespace name matches the file name (e.g., t1 ). If a shared tablespace is created with a name such as my_tablespace , that name becomes the tablespace identifier. All tablespaces are identified internally by a unique tablespace ID.

Tablespace files consist of a fixed‑size array of pages. Pages are the basic unit of storage; different page types serve different purposes. The collection of pages forms the tablespace.

Pages are grouped into extents. An extent is a contiguous set of pages that occupies 1 MiB. With a default page size of 16 KiB, each extent contains 64 pages. Thus, a tablespace file can be viewed as a series of extents.

The first page (page 0) is the header page, which stores metadata for the entire tablespace.

Metadata about free and used extents is kept in several linked lists:

Free Fragments List (FREE FRAGS LIST) – points to extents that have at least one free page.

Full Fragments List (FULL FRAGS LIST) – points to extents that have no free pages.

Free List (FREE LIST) – points to extents that are completely free and can be allocated to file segments.

XDES entries – each entry (40 bytes for a 16 KB page) describes 64 pages; 256 entries fit in one XDES page.

When the number of extents exceeds what a single XDES page can track, a new XDES page is allocated to continue tracking additional extents.

The INODE page stores information about file segments (FSEG). A file segment is a logical unit that groups pages and extents for a particular purpose, such as a leaf or non‑leaf segment of a B‑tree index.

Each file segment contains several structures:

FRAG ARRAY – an array of 32 entries indicating how many single pages are allocated to the segment.

NOT FULL LIST – a list of extents that have at least one free page.

FULL LIST – a list of extents that are completely occupied.

FREE LIST – a list of extents that are entirely free.

File segments simplify page management: when a segment is dropped, all its extents and pages can be reclaimed at once.

In InnoDB, each index (implemented as a B‑tree) uses two file segments: one for leaf pages and one for non‑leaf (internal) pages. The FSEG header stored in the page header points to the corresponding INODE entries.

When an index is created, the root page is allocated in the non‑leaf segment, while the leaf segment starts empty. As the index grows, pages are first allocated from the FRAG ARRAY, then from extents that move through the FREE LIST, NOT FULL LIST, and finally the FULL LIST, with new extents allocated as needed.

When an index is dropped, the two file segments associated with it are marked as free, and all their extents become available for reuse.

Overall, InnoDB tablespace management relies on a hierarchy of pages, extents, XDES entries, and file segments to efficiently allocate, track, and reclaim storage for tables and indexes.