InnoDB Tablespaces, Pages, Extents, Segments, and Row Formats

Learning outcomes

A MySQL table looks simple from SQL: columns, rows, keys, and indexes. InnoDB has to place those logical objects into durable storage. If you skip that layer, later advice about “pages,” “row formats,” “tablespace growth,” or “off-page values” sounds like unrelated vocabulary. This lesson builds one operational hierarchy and then verifies it with server metadata rather than asking you to memorize file names.

The disposable servicehub_innodb_lab continues the course’s field-service domain. We deliberately include a large diagnostic note so you can reason about variable-length data without manually opening or editing InnoDB files.

Build the Chapter 07 storage lab

Run the setup with an administrative local account on a disposable MySQL Community Server instance. The lab explicitly uses ENGINE=InnoDB and ROW_FORMAT=DYNAMIC so the storage assumptions are visible even if an administrator has customized defaults.

DROP DATABASE IF EXISTS servicehub_innodb_lab;CREATE DATABASE servicehub_innodb_lab  CHARACTER SET utf8mb4  COLLATE utf8mb4_0900_ai_ci;USE servicehub_innodb_lab;CREATE TABLE work_orders (  work_order_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,  request_key VARCHAR(64) NOT NULL,  customer_name VARCHAR(120) NOT NULL,  status VARCHAR(16) NOT NULL DEFAULT 'open',  priority TINYINT UNSIGNED NOT NULL DEFAULT 2,  opened_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,  PRIMARY KEY (work_order_id),  UNIQUE KEY uq_work_orders_request_key (request_key),  KEY ix_work_orders_status_opened (status, opened_at, work_order_id)) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;CREATE TABLE work_order_notes (  note_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,  work_order_id BIGINT UNSIGNED NOT NULL,  note_kind VARCHAR(24) NOT NULL,  note_text LONGTEXT NOT NULL,  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,  PRIMARY KEY (note_id),  KEY ix_notes_work_order_created (work_order_id, created_at, note_id),  CONSTRAINT fk_notes_work_order FOREIGN KEY (work_order_id)    REFERENCES work_orders(work_order_id)) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;INSERT INTO work_orders(request_key,customer_name,status,priority) VALUES ('REQ-7001','Northwind Clinic','open',1), ('REQ-7002','City Library','assigned',2), ('REQ-7003','Harbor Foods','open',3);INSERT INTO work_order_notes(work_order_id,note_kind,note_text) VALUES (1,'arrival','Technician checked in at reception.'), (1,'diagnostic',REPEAT('diagnostic detail ',1500)), (2,'arrival','Technician met the facilities manager.');

Verify the logical definition first. SHOW CREATE TABLE tells you the server’s effective table definition, including engine, row format, keys, and foreign keys.

SHOW CREATE TABLE work_orders\GSHOW CREATE TABLE work_order_notes\GSELECT VERSION() AS server_version, @@innodb_page_size AS innodb_page_size,       @@innodb_file_per_table AS file_per_table,       @@innodb_default_row_format AS default_row_format;

On a normal 8.4 instance, innodb_page_size is commonly 16384 bytes (16 KiB), innodb_file_per_table is normally enabled, and DYNAMIC is the default row format. Treat the query result—not a tutorial screenshot—as the fact for your instance.

From tablespace to page to extent to segment

A tablespace is an InnoDB logical storage container backed by one or more storage files depending on its type. A page is InnoDB’s fundamental unit for reading, writing, caching, and organizing many on-disk structures. The instance page size is chosen when the data directory is initialized and then remains fixed for the instance. An extent is a group of pages used for allocation. A segment is a higher-level allocation structure used by growing objects such as B-tree indexes.

For the common 4 KiB, 8 KiB, and 16 KiB page-size families, InnoDB uses 1 MiB extents. Larger configured page sizes use larger extents. That relationship is an implementation fact, not a recommendation to reinitialize an instance for a particular extent size.

Know the tablespace families

The simplest course lab stays with file-per-table. General tablespaces are important to recognize but are not required to learn ordinary InnoDB storage behavior.

SELECT NAME, SPACE, ROW_FORMAT, SPACE_TYPEFROM information_schema.INNODB_TABLESWHERE NAME LIKE 'servicehub_innodb_lab/%'ORDER BY NAME;SELECT SPACE, PATHFROM information_schema.INNODB_DATAFILESWHERE PATH LIKE '%servicehub_innodb_lab%';

INNODB_DATAFILES requires the PROCESS privilege. If a least-privilege application account cannot query it, that is expected; use an administrative diagnostic account rather than granting broad privileges to the application.

Row formats and large variable-length values

InnoDB supports REDUNDANT, COMPACT, DYNAMIC, and COMPRESSED row formats. DYNAMIC is the modern default. For sufficiently large variable-length columns, InnoDB can place data on overflow pages and keep enough information in the clustered record to locate it. Whether a particular value is stored fully inline, partly inline, or off-page depends on row format, value sizes, page constraints, and the rest of the row.

SELECT note_id, note_kind, OCTET_LENGTH(note_text) AS bytes_storedFROM work_order_notesORDER BY note_id;SELECT NAME, ROW_FORMAT, SPACE_TYPEFROM information_schema.INNODB_TABLESWHERE NAME='servicehub_innodb_lab/work_order_notes';

The long diagnostic note should report many thousands of bytes. That proves the SQL value is large; it does not prove a precise overflow-page count. Avoid pretending that OCTET_LENGTH() is a physical-storage inspector.

Failure drill: treating page size like a normal dynamic variable

A tempting mistake is to see innodb_page_size and try to tune it live:

SHOW VARIABLES LIKE 'innodb_page_size';SET GLOBAL innodb_page_size = 8192;

MySQL rejects the change because the page size is an initialization-time property, not a dynamic tuning setting. Changing it requires a separately initialized instance and a migration/reload strategy. The repair is not to find a more forceful command; it is to leave the instance intact unless you have measured evidence and a planned rebuild.

Hands-on lab: storage inventory, not storage folklore

Capture a small inventory that future lessons can reuse:

SELECT VERSION() AS version;SHOW VARIABLES WHERE Variable_name IN ('innodb_page_size','innodb_file_per_table','innodb_default_row_format');SELECT NAME, SPACE, ROW_FORMAT, SPACE_TYPEFROM information_schema.INNODB_TABLESWHERE NAME LIKE 'servicehub_innodb_lab/%'ORDER BY NAME;SELECT TABLE_NAME, ENGINE, ROW_FORMAT, TABLE_ROWS, DATA_LENGTH, INDEX_LENGTHFROM information_schema.TABLESWHERE TABLE_SCHEMA='servicehub_innodb_lab'ORDER BY TABLE_NAME;

TABLE_ROWS, DATA_LENGTH, and INDEX_LENGTH can be estimates for InnoDB. They are useful for scale and trend, not exact accounting. Record them as observations, then compare after later labs grow tables.

Reason about allocation without turning internals into a tuning superstition

Pages and extents matter because B-tree indexes grow over time. An index does not allocate an infinitely large contiguous region when the table is created. As records arrive, InnoDB splits and allocates pages and acquires additional space through its tablespace allocation mechanisms. This means file growth is normally a consequence of the logical objects inside the tablespace, not evidence that one particular SQL row “used a whole extent.” A small table can still occupy multiple pages because index roots, metadata, free space, and future-growth room are part of the structure.

Free space has more than one meaning. There can be unused room inside an index page, pages available inside a tablespace, and filesystem space outside the tablespace. Those layers should not be collapsed into one number. Deleting rows can make page or tablespace space reusable by InnoDB without necessarily returning bytes to the operating system immediately. File-per-table tablespaces have lifecycle operations that can return table-level space in ways shared general/system spaces cannot. Chapter 18 will revisit large-table reclamation and lifecycle operations from an operational perspective.

Optional administrative experiment: a general tablespace

If your local account has the required CREATE TABLESPACE privilege, create a general tablespace only on the disposable lab instance and place one tiny table in it. Then compare SPACE_TYPE metadata with the file-per-table objects. This is optional because ordinary application accounts should not receive tablespace-administration privileges.

CREATE TABLESPACE servicehub_general ENGINE=InnoDB;CREATE TABLE general_space_demo (id INT PRIMARY KEY, note VARCHAR(80))  ENGINE=InnoDB TABLESPACE servicehub_general;SELECT NAME, SPACE_TYPE, ROW_FORMATFROM information_schema.INNODB_TABLESWHERE NAME='servicehub_innodb_lab/general_space_demo';DROP TABLE general_space_demo;DROP TABLESPACE servicehub_general;

Notice the cleanup order: remove tables that live in the general tablespace before dropping the tablespace itself. The experiment is about recognizing shared versus per-table placement; it is not a recommendation to move production tables into a general tablespace.

Production judgment and bridge

Most teams should not choose page size, row format, or tablespace type by folklore. The operational questions are narrower: Is file-per-table appropriate for lifecycle/reclaim needs? Are rows becoming so wide that large-value I/O matters? Are shared general tablespaces intentional? Does a migration require transportable tablespaces or a special initialization choice? Measure those requirements before changing engine defaults.

Lesson 2 zooms into the B-tree organization inside those spaces: the clustered index, secondary indexes, hidden clustered identifiers, and why your primary-key design is physically repeated through secondary indexes.

Authoritative references

• MySQL 8.4 Reference Manual — InnoDB Tablespaces
• MySQL 8.4 Reference Manual — InnoDB Row Formats
• MySQL 8.4 Reference Manual — Creating InnoDB Tables
• MySQL 8.4 Reference Manual — INNODB_TABLES
• MySQL 8.4 Reference Manual — INNODB_DATAFILES