Skip to content
Tutorials Center
My learning

Topics

Lesson 41 of 75 ยท Storage Engines and Internals

InnoDB Architecture

See how InnoDB stores and protects data: the clustered index, 16 KB pages, buffer pool, redo and undo logs, doublewrite buffer, change buffer and row formats.

InnoDB is the storage engine behind almost every MySQL table you will ever create. SQL decides what you want. InnoDB decides how rows are laid out on disk, how they are cached in memory, and how a crash in the middle of a write is survived. Once you know its few moving parts, a lot of MySQL advice (keep primary keys short, size the buffer pool, do not hold transactions open) stops being folklore and becomes obvious.

This lesson is a guided tour. The server internals themselves cannot run in the browser, so the architecture is shown with diagrams and read-only statements. Two live examples use SQLite as an honest stand-in: SQLite also stores tables as B-trees in fixed-size pages, so you can see the same shapes, though the details differ from InnoDB.

Two worlds

In memory Buffer pool, log buffer, change buffer.
On disk Tablespaces, redo log, undo, doublewrite files.

Reads and writes happen in memory first. Disk catches up later.

1 / 2

The clustered index: the table is the primary key

In InnoDB every table is a B+tree ordered by the primary key. This structure is called the clustered index, and its leaf pages hold the complete rows. There is no separate heap of rows with an index pointing into it.

  • If you declare a PRIMARY KEY, that is the clustered key.
  • If you do not, InnoDB uses the first UNIQUE index whose columns are all NOT NULL.
  • If there is none, InnoDB invents a hidden 6-byte row id and clusters on that. You cannot use it in queries, and it is a shared counter, so always declare a real primary key.

Each stored row also carries two hidden system columns: a 6-byte transaction id (DB_TRX_ID, who last changed the row) and a 7-byte roll pointer (DB_ROLL_PTR, where its previous version lives in the undo log). These two are the basis of MVCC, covered in Isolation Levels and MVCC.

A secondary index is another B+tree whose leaf entries hold the indexed columns plus the primary key value. To fetch a full row through a secondary index, InnoDB searches the secondary tree, reads the primary key from the leaf, then searches the clustered tree (a "bookmark lookup"). Two consequences follow:

  1. Lookups by primary key are the fastest possible: one tree descent reaches the whole row.
  2. A wide primary key is copied into every secondary index, so a 36-character UUID string as the key inflates all of them. See Choosing the Right Data Types.

SQLite's WITHOUT ROWID tables are clustered on the primary key in the same way, so they make a good stand-in:

SQL Clustered key versus secondary index

Press Run (or Ctrl + Enter) to see the result here.

Plan 3 says COVERING INDEX: the secondary index stores email automatically because it is the primary key. InnoDB behaves identically, and in MySQL's EXPLAIN you would see Using index in the Extra column.

Pages: the unit of everything

InnoDB never reads or writes single rows on disk. It works in pages, 16 KB by default (innodb_page_size, fixed when the server is initialized). Leaf pages hold rows, branch pages hold key ranges that point to child pages, and all pages of one level are linked in a doubly linked list so range scans can walk sideways. Pages are grouped into 1 MB extents (64 pages), and an index tries to allocate extents so its pages stay near each other on disk.

A page with, say, 100 rows is read from disk as one block, and the same block is what sits in memory. Because each branch page can point to over a thousand children when keys are small, a clustered index of a hundred million rows is typically only three or four levels deep. A primary key lookup is a handful of page reads, and the top levels are almost always already in memory.

Inserting in key order fills pages neatly (InnoDB leaves a little free space, controlled by innodb_fill_factor). Inserting random keys, such as random UUIDs, splits full pages at arbitrary places, leaving pages about half full and scattering writes. That is why ordered ids are recommended in AUTO_INCREMENT, UUIDs and ID Strategies.

You can watch page counts grow in SQLite, which also uses fixed-size pages:

SQL Rows live in pages

Press Run (or Ctrl + Enter) to see the result here.

The page size here is SQLite's (4 KB), not InnoDB's 16 KB, but the principle is identical: the file is an array of pages, and 2,000 small rows occupy a few dozen of them.

The buffer pool: InnoDB's cache

The buffer pool holds copies of data and index pages in RAM. Every read goes through it, and every change is made to the in-memory page first, which makes the page dirty. It is by far the most important memory setting on a dedicated database server, and its size is innodb_buffer_pool_size (the default of 128 MB is only a starting point; a common production rule is 50% to 75% of RAM on a dedicated host).

Eviction uses a modified LRU list split into a "young" and an "old" part. New pages enter at the midpoint, not at the head, so one huge table scan cannot flush out your hot working set. A page is promoted to the young end only if it is touched again. Useful statements:

SQL
SELECT @@innodb_buffer_pool_size / 1024 / 1024 AS buffer_pool_mb;
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
-- Innodb_buffer_pool_read_requests: logical reads (served from memory or not)
-- Innodb_buffer_pool_reads:         reads that had to go to disk

A healthy OLTP server satisfies well over 99% of read requests from memory. These statements are read-only here because they need a running MySQL server.

Write path: redo, undo, doublewrite

When a transaction changes a row, InnoDB does not write the changed page to its data file at commit time. That would mean slow random writes. Instead:

  1. It copies the old row version into the undo log.
  2. It changes the page in the buffer pool (the page becomes dirty).
  3. It records the change in the redo log, first in an in-memory log buffer.
  4. At COMMIT, it flushes the redo log to disk. This is a fast sequential append. Once it is on disk, the transaction is durable.
  5. Much later, background threads write dirty pages to the data files.
flowchart LR
  A["UPDATE row"] --> B["Undo record<br/>(old version)"]
  A --> C["Page changed in<br/>buffer pool (dirty)"]
  C --> D["Redo log buffer"]
  D -->|"COMMIT: fsync"| E[("Redo log on disk")]
  C -.->|"later, in background"| F["Doublewrite<br/>area"]
  F --> G[("Data file<br/>.ibd")]

A commit waits only for the sequential redo write. Data pages reach their files later.

This is write-ahead logging. If the server crashes, on restart InnoDB replays the redo log to redo committed changes that never reached the data files, and uses the undo log to roll back transactions that had not committed. The position in the redo log is the LSN (log sequence number), and a checkpoint records how far the data files are known to be up to date.

In MySQL 8.0.30 and later the redo log lives in a #innodb_redo directory and its total size is set with innodb_redo_log_capacity. Older versions used two ib_logfile files sized by innodb_log_file_size. A redo log that is too small forces frequent checkpoints and stalls writes.

How hard InnoDB flushes at commit is set by innodb_flush_log_at_trx_commit: 1 (the default) flushes the redo log to disk on every commit and is fully durable, 2 writes to the operating system but flushes about once a second, and 0 flushes about once a second. Values 2 and 0 can lose roughly the last second of commits on a crash.

The undo log has two jobs: roll back a transaction on ROLLBACK, and serve old versions of rows to other transactions that still need to see them (MVCC). Undo records live in undo tablespaces (two by default, undo_001 and undo_002). A background purge thread deletes undo records once no transaction can need them. A transaction left open for hours stops purge, the undo history grows (watch "History list length" in SHOW ENGINE INNODB STATUS), and everything slows down.

The doublewrite buffer protects against torn pages. A 16 KB page is written as several smaller operating-system blocks, and power loss can leave a page half old, half new. Redo records describe changes to a page, so they cannot repair a corrupt page. InnoDB therefore writes pages to a doublewrite area first, and only then to their real place. After a crash, a torn page is restored from its intact doublewrite copy and redo is applied on top. Since 8.0.20 the area is in separate #ib_*.dblwr files.

The change buffer

Secondary indexes cause a problem: inserting a row touches the clustered index, but also one leaf page in each secondary index, and those pages are usually in random places and often not in memory. For non-unique secondary indexes, InnoDB can skip the disk read. It records the change in the change buffer and merges it into the real page later, when that page is read anyway or by a background merge. This makes write-heavy workloads on many indexes much cheaper. Unique indexes cannot use it because InnoDB must read the page to check for duplicates. It is controlled by innodb_change_buffering and innodb_change_buffer_max_size (25% of the buffer pool by default).

A related feature is the adaptive hash index, an automatic in-memory hash built over hot index pages to speed up equality lookups. It is on by default and managed by InnoDB.

Tablespaces and row formats

Data is stored in tablespaces:

Tablespace What it holds
File-per-table (schema/table.ibd) One table and its indexes. The default (innodb_file_per_table=ON).
System (ibdata1) Change buffer storage and some internal structures. Tables are no longer stored here by default.
General (CREATE TABLESPACE) Several tables of your choice in one file.
Undo (undo_001, ...) Undo logs.
Temporary Temporary tables and sort files.

File-per-table is the default, so DROP TABLE or OPTIMIZE TABLE can give disk space back to the operating system. The data dictionary itself is also kept in InnoDB tables since MySQL 8.0, as you will see in The Data Dictionary, information_schema and sys Schema.

A table's row format controls how rows are stored inside pages. The default is DYNAMIC. COMPACT and REDUNDANT are older, and COMPRESSED also compresses pages.

SQL
SHOW TABLE STATUS LIKE 'orders'\G
SELECT table_name, row_format FROM information_schema.tables
WHERE table_schema = 'shop';

CREATE TABLE notes (
  id   INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  body TEXT
) ROW_FORMAT=DYNAMIC;

With DYNAMIC, long values of TEXT, BLOB and large VARCHAR columns are stored off-page in overflow pages, leaving only a 20-byte pointer in the row. Rows in the clustered index therefore stay small, and scans that do not need the big column are cheap. That is one reason to avoid SELECT * on tables with large text columns. A row that stays in the page cannot be larger than about half a page (roughly 8 KB).

Every statement in code blocks marked as MySQL syntax in this lesson is read-only. Only the two TryIt examples run, on SQLite. SQLite is a rowid- or key-ordered B-tree in a single file, without a buffer pool, undo log or MVCC of InnoDB's kind, so use it to understand shapes, not behavior.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does an InnoDB secondary index leaf entry contain besides the indexed columns?

    Secondary index entries hold the primary key, which InnoDB uses to look up the full row in the clustered index. That is why a wide primary key makes every secondary index bigger.
  2. 2. At COMMIT time with innodb_flush_log_at_trx_commit=1, what is written to disk?

    Durability comes from flushing the redo log. Dirty data pages are written later by background threads, and redo replay repairs any that never made it.
  3. 3. Which component protects against a data page that was only half written when the power failed?

    Pages are first written to the doublewrite area, so a torn page can be restored from its intact copy and then brought up to date from the redo log.
  4. 4. Why does a transaction that stays open for hours slow InnoDB down?

    Old versions needed by a long-running snapshot cannot be purged, so undo grows and reads must walk longer version chains.

Summary

  • An InnoDB table is a B+tree clustered on the primary key, and leaf pages hold whole rows. Always declare a short primary key.
  • Secondary indexes store the primary key, so lookups by them take two tree descents unless the index is covering.
  • Everything works in 16 KB pages, cached in the buffer pool, whose size (innodb_buffer_pool_size) is the key memory setting.
  • Commits are durable through the sequential redo log. Data pages are flushed later, protected from torn writes by the doublewrite buffer.
  • The undo log supports rollback and MVCC, and long transactions stop its purge.
  • The change buffer defers non-unique secondary index writes. DYNAMIC rows push big text and blob values off-page.

Next: The Data Dictionary, information_schema and sys Schema, where you learn to ask the server about its own tables and indexes.