How MySQL Works Internally: InnoDB Storage, Buffer Pool, and the Redo Log Behind the World's Most Popular Open-Source Database

MySQL is the most widely deployed open-source relational database on the planet. It powers WordPress installations, financial platforms, e-commerce backends, and a large fraction of the internet’s read-heavy OLTP workloads. But most engineers interact with it entirely through SQL and ORMs, leaving the internals as a black box.

Understanding what InnoDB does below the query layer changes how you design schemas, choose indexes, size servers, and diagnose production incidents. This article covers the five systems that matter most: the clustered index storage engine, the buffer pool, MVCC via undo logs, the redo log with its doublewrite buffer, and the binary log that drives replication. Each one has design decisions that differ meaningfully from PostgreSQL, and those differences have real consequences at production scale.

The InnoDB Storage Engine: Everything Is a B+ Tree

Unlike PostgreSQL, which stores tables in heap files and uses a separate index structure to point back into the heap, InnoDB stores all table data directly inside a B+ tree indexed by the primary key. This structure is called the clustered index, and it is the most important concept for understanding InnoDB performance.

Clustered Index Layout

In InnoDB, the primary key is not just an index: it is the table. Rows are stored in leaf nodes of the B+ tree, physically ordered by primary key value. The internal nodes hold separator keys and child page pointers. Pages are 16 KB by default.

B+ Tree (clustered index, PK = user_id)

Internal node:  [ 100 | 500 | 1000 ]
                 /     |     |      \
Leaf pages:  [1..99] [100..499] [500..999] [1000+]
             (full row data stored in leaf pages)

Each leaf page stores the complete row: all columns, including blobs and large text that fit within the page. If a row is too large, overflow pages are allocated and the leaf node stores a pointer to the off-page data (similar to PostgreSQL’s TOAST, but structured differently within the B+ tree).

This design has a critical implication: rows with adjacent primary key values are physically co-located on the same or adjacent pages. Range queries on the primary key are sequential reads. But secondary indexes work differently.

Secondary Indexes and Covering Index Mechanics

Every secondary index in InnoDB stores the indexed columns plus the primary key value of the row. There is no direct pointer to the physical page location of the row (no ctid equivalent). To fetch non-indexed columns using a secondary index, InnoDB performs a double lookup: first traverse the secondary B+ tree to find the primary key, then traverse the clustered index to get the full row.

-- Table: orders(id PK, customer_id, status, amount, created_at)
-- Secondary index: idx_customer on (customer_id)

SELECT id, amount FROM orders WHERE customer_id = 42;
-- Step 1: scan idx_customer for customer_id = 42 -> returns [id: 101, 205, 891]
-- Step 2: for each id, look up clustered index -> full row fetch

This double lookup can be expensive for high-cardinality secondary index scans. The solution is a covering index: an index that includes all columns the query needs, so InnoDB can return results from the secondary index leaf nodes without touching the clustered index at all.

-- Covering index for the query above
CREATE INDEX idx_customer_covering ON orders (customer_id, id, amount);

-- Now the query is satisfied entirely from the secondary index:
-- EXPLAIN shows "Using index" in the Extra column

The choice of primary key has significant performance consequences. Auto-increment integer PKs (INT AUTO_INCREMENT or BIGINT) produce monotonically increasing values, which means new rows always append to the rightmost leaf page. This is optimal for write throughput: the B+ tree grows without requiring page splits in the middle of the tree. UUID primary keys, by contrast, insert at random positions, causing frequent page splits, B+ tree rebalancing, and fragmentation. At high insert rates, UUID PKs on InnoDB produce measurably worse write throughput and higher disk usage than sequential PKs.

The Buffer Pool

The buffer pool is InnoDB’s primary in-memory cache. It is the central performance component of MySQL. All reads and writes pass through it: InnoDB reads a page from disk into the buffer pool, modifies it in memory, and eventually writes the dirty page back to disk. Hot pages stay in memory and are served without disk I/O on subsequent reads.

LRU List and the Midpoint Insertion Strategy

InnoDB uses a modified LRU (Least Recently Used) algorithm to manage the buffer pool. The pool is divided into two sublists:

Buffer Pool LRU List

+-------------------+-------------------+
|    Young sublist  |    Old sublist    |
|  (hot pages, 5/8) | (new pages, 3/8)  |
+-------------------+-------------------+
         ^                   ^
         |                   |
  promoted here     inserted here first

When a page is read from disk, it enters at the midpoint (the boundary between young and old sublists), not the head of the LRU. This prevents a full table scan from evicting all hot pages in the young sublist. A page is only promoted to the young sublist (hot) if it is accessed again while still in the old sublist, which requires surviving at least innodb_old_blocks_time milliseconds (default 1000ms) in the old sublist.

This design means a single large analytical query scanning a cold table does not flush all cached OLTP data from memory, which is the most common cause of buffer pool thrashing in mixed workloads.

Dirty Page Flushing

Modified pages in the buffer pool are called dirty pages. InnoDB flushes them to disk through two mechanisms:

Checkpoint flushing: InnoDB periodically flushes dirty pages as part of a checkpoint operation, reducing the redo log span that would need to be replayed on crash recovery. The innodb_io_capacity parameter controls how aggressively InnoDB flushes dirty pages in the background.

LRU flushing: when the buffer pool needs free pages and the LRU tail contains dirty pages, InnoDB must flush before evicting. If this happens too frequently, you see stalls in write latency. Monitoring Innodb_buffer_pool_wait_free reveals how often this occurs.

Adaptive Hash Index

InnoDB monitors which clustered index or secondary index lookups are repeated frequently. When a particular B+ tree traversal path is detected as hot (accessed repeatedly with the same key), InnoDB automatically builds an in-memory hash index mapping that key directly to the leaf page. This converts a multi-level B+ tree traversal (typically 3-5 page reads for a large table) into a single hash lookup.

The adaptive hash index (AHI) operates entirely in memory, requires no schema changes, and is built and discarded automatically. You can observe it:

SHOW ENGINE INNODB STATUS\G
-- Look for: Hash table size X, node heap has Y buffer(s)
-- Hash searches/s, non-hash searches/s

The AHI can become a contention point under highly concurrent workloads because it is protected by internal latches. If SHOW ENGINE INNODB STATUS shows high AHI latch waits, disabling it (innodb_adaptive_hash_index = OFF) is sometimes the right call.

MVCC via the Undo Log

InnoDB implements Multi-Version Concurrency Control differently from PostgreSQL in a way that has significant operational consequences.

The Undo Log Approach vs. PostgreSQL’s Heap Approach

PostgreSQL writes all versions of a row directly into the heap. When a row is updated, the old version stays in the heap with a tombstone flag, and the new version is inserted. Vacuuming later reclaims the space. Old versions scatter throughout the heap files.

InnoDB takes the opposite approach. The clustered index always holds the current version of each row. When a row is updated, InnoDB writes a copy of the old row data to the undo log, a separate segment within the InnoDB tablespace. Transactions that need to see an older version reconstruct it by reading the current row and applying undo log entries in reverse.

Clustered Index Leaf:
  Row (id=42): name="Alice Updated", balance=500   <- current version

Undo Log:
  Undo record: name="Alice", balance=600           <- previous version
  Undo record: name="Alice", balance=700           <- even older version

This means InnoDB’s clustered index never contains dead versions. The index stays compact. There is no equivalent of PostgreSQL’s heap bloat from dead tuples. However, long-running transactions that hold a snapshot anchored to an old read view prevent undo log records from being purged. If a transaction reads at a point in time when balance=700 was current, InnoDB cannot discard any undo records newer than that snapshot. The undo log grows without bound until the transaction commits or rolls back.

This is the InnoDB equivalent of PostgreSQL’s n_dead_tup problem, but it lives in the undo tablespace rather than the heap.

Read Views and Snapshot Isolation

When a transaction starts in REPEATABLE READ isolation (the MySQL default), InnoDB creates a read view that records:

• The current transaction’s ID
• The IDs of all currently active transactions
• The minimum and maximum transaction IDs in flight

When reading a row, InnoDB checks whether the row’s transaction ID falls within the visible range. If the current version was written by a transaction that started after this read view was created, InnoDB follows the undo log chain back in time until it finds a version that was committed before the read view was established.

-- Session A: long-running analytics query
START TRANSACTION;
SELECT COUNT(*) FROM orders;   -- Read view established here

-- Session B: high-frequency inserts running concurrently
INSERT INTO orders ...;        -- New orders appear in undo log, not visible to A

-- The undo log entries from B cannot be purged until A commits
COMMIT;  -- A finishes, undo entries become eligible for purge

Monitoring for this condition uses SHOW ENGINE INNODB STATUS under “TRANSACTIONS”:

-- Look for transactions with large "history list length"
SHOW ENGINE INNODB STATUS\G

-- Or query the information schema directly
SELECT trx_id, trx_started, trx_mysql_thread_id,
       TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS seconds_open
FROM information_schema.innodb_trx
ORDER BY trx_started ASC;

Any transaction open for more than a few minutes in a write-heavy environment is a problem.

The Redo Log and Doublewrite Buffer

InnoDB guarantees durability through a write-ahead log called the redo log. Before any page modification is applied to the buffer pool, a redo log record describing the change is written to the redo log buffer and flushed to disk. On crash, InnoDB replays the redo log to bring the database to the last committed state.

Redo Log Mechanics

The redo log is stored in one or more files (ib_logfile0, ib_logfile1 in older MySQL versions; a single #ib_redo* set of files in MySQL 8.0+). It is a circular ring buffer. InnoDB writes to it sequentially, wrapping around when it reaches the end. If the log fills up before dirty pages are flushed to disk, InnoDB stalls writes until checkpointing frees space.

Redo log (circular):

[ ... LSN 1001 | LSN 1002 | LSN 1003 | ... | LSN 5000 ]
               ^                              ^
        checkpoint LSN                  current write position
        (pages flushed up to here)

The gap between the checkpoint LSN and the current write position represents the redo log span that would need to be replayed on crash. A large span means longer crash recovery. Tuning innodb_log_file_size (or innodb_redo_log_capacity in MySQL 8.0+) to be large enough to absorb write bursts without forcing premature checkpointing is a standard production tuning step.

innodb_flush_log_at_trx_commit controls the durability-throughput tradeoff:

• 1 (default): flush redo log to disk on every commit. Full ACID durability. Safest.
• 2: write to OS page cache on every commit, flush once per second. Survives MySQL crash but not OS crash.
• 0: write and flush once per second. Can lose up to one second of commits on any crash. Highest throughput, weakest durability guarantee.

The Doublewrite Buffer

InnoDB uses 16 KB pages. Most Linux filesystems use 4 KB blocks. A partial write (a power failure mid-way through writing a 16 KB page) can leave a torn page on disk: the first 4 KB of the new version and the last 8 KB of the old version, producing a corrupt page that the redo log cannot safely recover.

The doublewrite buffer is InnoDB’s solution. Before writing dirty pages to their actual locations on disk, InnoDB first writes them to a sequential doublewrite area (two 1 MB extents, stored in the system tablespace or a dedicated file in MySQL 8.0.20+). Once the doublewrite write is confirmed durable, InnoDB proceeds to write the pages to their actual tablespace locations.

On crash recovery, InnoDB checks each page. If a page on disk is torn (checksum mismatch), InnoDB reads the intact copy from the doublewrite buffer and uses that for redo log replay instead.

This adds roughly one extra sequential write for every random write to tablespace pages. The overhead is typically 5-10% on spinning disks and lower on SSDs because of the sequential nature of the doublewrite writes.

The Query Optimizer

MySQL’s query optimizer is cost-based: it estimates the cost of candidate execution plans using statistics and picks the lowest-cost plan. The optimizer’s behavior is shaped by index statistics, table cardinality estimates, and several heuristics that differ from PostgreSQL.

Index Statistics and ANALYZE TABLE

InnoDB maintains per-index statistics: the number of distinct values per prefix of the index key, and the number of leaf pages. These are stored in the data dictionary and updated by ANALYZE TABLE or automatically when a significant fraction of the table changes.

-- Inspect index statistics
SELECT index_name, stat_name, stat_value
FROM mysql.innodb_index_stats
WHERE database_name = 'mydb' AND table_name = 'orders'
ORDER BY index_name, stat_name;

stat_value for n_diff_pfx01 is the estimated number of distinct values in the first column of the index. When this estimate is significantly off (due to data skew or statistics not being refreshed), the optimizer can choose a suboptimal index or perform an unnecessary full table scan.

EXPLAIN and Execution Plans

EXPLAIN and EXPLAIN ANALYZE are the primary tools for diagnosing query plans:

EXPLAIN FORMAT=TREE
SELECT o.id, o.amount, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'pending'
  AND o.created_at > NOW() - INTERVAL 7 DAY
ORDER BY o.created_at DESC
LIMIT 100;

Key columns in the output:

• type: const (single row by PK), ref (index lookup), range (index range scan), index (full index scan), ALL (full table scan). Seeing ALL on a large table is a signal to add an index.
• key: the index actually chosen.
• rows: estimated row count the optimizer expects to examine.
• Extra: Using index means covering index. Using filesort means an in-memory or on-disk sort not satisfied by an index. Using temporary means a temporary table was created.

EXPLAIN ANALYZE runs the query and shows actual versus estimated rows, revealing where the optimizer’s estimates diverged from reality.

Replication: Binary Log and GTID-Based Replication

MySQL replication is driven by the binary log (binlog), a separate log from the InnoDB redo log. The binlog records all changes to the database as an ordered sequence of events that replicas can replay to stay consistent with the primary.

Binary Log Formats

The binlog supports three formats, set by binlog_format:

• STATEMENT: logs the original SQL statement. Compact, but non-deterministic statements (NOW(), RAND(), UUID()) can produce different results on replicas.
• ROW: logs the actual row changes (before and after images). Larger log volume but deterministic and safe for all statement types.
• MIXED: uses statement logging when safe, row logging otherwise. MySQL 8.0 defaults to ROW.

GTID-Based Replication

Global Transaction Identifiers (GTIDs) assign a unique ID to every transaction committed on the primary. A GTID is a source_uuid:transaction_id pair:

3E11FA47-71CA-11E1-9E33-C80AA9429562:1-100

GTID-based replication eliminates the need to track binlog file names and byte offsets for replica positioning. Each replica tracks the set of GTIDs it has applied. On reconnect or failover, the replica sends its GTID set to the new primary, and the primary automatically identifies and sends only the missing transactions.

-- On the primary: check GTID state
SHOW MASTER STATUS\G
-- Executed_Gtid_Set: 3E11FA47-71CA-11E1-9E33-C80AA9429562:1-10000

-- On the replica: check replication lag and GTID state
SHOW REPLICA STATUS\G
-- Retrieved_Gtid_Set:  (what we've received)
-- Executed_Gtid_Set:   (what we've applied)
-- Seconds_Behind_Source: replication lag in seconds

GTID-based replication is the recommended mode for production MySQL 8.0+ deployments. It simplifies failover (tools like Orchestrator and MySQL Router use GTIDs for automatic promotion), makes replica consistency verification easier, and eliminates the class of bugs caused by stale binlog coordinates after a topology change.

Tradeoffs: MySQL vs. PostgreSQL vs. CockroachDB vs. PlanetScale

Dimension	MySQL (InnoDB)	PostgreSQL	CockroachDB	PlanetScale
Storage model	B+ tree clustered by PK; all data in clustered index	Heap files; separate B-tree indexes pointing into heap	Distributed RocksDB LSM-tree; Raft per range	MySQL-compatible; Vitess sharding over InnoDB
MVCC implementation	Undo log; current version always in clustered index; old versions reconstructed	All versions written to heap; vacuum reclaims dead tuples	MVCC over distributed Raft log; intent records	Same as InnoDB; Vitess adds cross-shard coordination
Dead tuple / bloat risk	Undo log bloat from long-running transactions	Heap bloat from MVCC dead tuples; requires autovacuum	MVCC GC handled automatically by distributed garbage collection	InnoDB risk applies; Vitess adds schema change tooling
Default isolation	REPEATABLE READ	READ COMMITTED	SERIALIZABLE	REPEATABLE READ
Horizontal scaling	Vertical + read replicas; sharding requires Vitess or app logic	Similar; Citus for sharding	Native horizontal sharding; geo-distributed writes	Native horizontal sharding via Vitess; branching workflow
Schema changes	Online DDL via InnoDB instant DDL (limited) or pt-online-schema-change	Transactional DDL; ALTER TABLE locks table	Online schema changes; minimal locking	Non-blocking schema changes via Vitess shadow tables
Replication	Binary log + GTID; synchronous or asynchronous	WAL streaming; synchronous or asynchronous	Raft consensus; synchronous by design	Vitess replication topology with automated failover
Best fit	Read-heavy OLTP, web applications, large ecosystem, many ORMs and managed offerings	Complex queries, mixed OLTP/analytics, PostGIS, JSON workloads	Multi-region distributed OLTP needing strong consistency	MySQL workloads needing horizontal scale without managing Vitess directly

Production Considerations

Buffer pool sizing. The single highest-impact MySQL tuning parameter. Set innodb_buffer_pool_size to 70-80% of available RAM. On a 64 GB server, that means 45-50 GB. A buffer pool too small relative to your working set causes constant disk I/O for data that should be cached. Monitor Innodb_buffer_pool_read_requests versus Innodb_buffer_pool_reads: the ratio should be > 99% for a well-sized pool.

SELECT
  variable_name,
  variable_value
FROM performance_schema.global_status
WHERE variable_name IN (
  'Innodb_buffer_pool_read_requests',
  'Innodb_buffer_pool_reads',
  'Innodb_buffer_pool_pages_dirty',
  'Innodb_buffer_pool_pages_free'
);

Connection management. MySQL uses a thread-per-connection model. At 1000 connections, you have 1000 threads, each consuming memory and CPU for context switching. The max_connections default (151) is too low for most web applications. A connection pooler (ProxySQL or MySQL Router) is required at scale. ProxySQL also provides query routing, read-write splitting, and query caching as a side effect of pooling.

Long transaction monitoring. Long-running transactions prevent undo log purging, causing undo log growth and slowing down row version chain traversal for all queries on affected tables. Set innodb_undo_log_truncate = ON and monitor transaction age:

SELECT trx_id,
       trx_mysql_thread_id,
       TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS age_seconds,
       trx_rows_locked,
       trx_rows_modified,
       LEFT(trx_query, 200) AS query_snippet
FROM information_schema.innodb_trx
WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 30
ORDER BY age_seconds DESC;

Any transaction older than 30-60 seconds in an OLTP system warrants investigation. Automatic kill via pt-kill from Percona Toolkit is a common production safeguard.

Redo log capacity. Too small a redo log forces frequent checkpointing, which generates I/O spikes. A good baseline is enough capacity to absorb 60-90 seconds of write load. In MySQL 8.0+, set innodb_redo_log_capacity directly:

-- Check current redo log usage
SELECT variable_name, variable_value
FROM performance_schema.global_status
WHERE variable_name IN (
  'Innodb_redo_log_capacity_resized',
  'Innodb_redo_log_checkpoint_lsn',
  'Innodb_redo_log_current_lsn'
);

Primary key selection. Use auto-increment integer PKs on write-heavy tables. Random UUIDs as PKs cause B+ tree page splits at every insert, measurably degrading write throughput and increasing tablespace fragmentation over time. If you need globally unique identifiers for external-facing IDs, generate UUIDs as a separate column with a separate unique index, and use the auto-increment PK as the actual clustered index key.

Schema change safety. MySQL’s ALTER TABLE can lock tables entirely depending on the operation. Use pt-online-schema-change or gh-ost for schema changes on large production tables. Both tools perform the migration by creating a shadow table, copying data in the background, and applying binlog changes to keep the shadow table in sync, then swapping the tables atomically.

The Design Choices That Define InnoDB

The clustered index is the axis around which everything else in InnoDB rotates. Row storage, secondary index design, primary key choice, and range query performance all follow from it. The buffer pool is not a passive cache; it is the central I/O coordinator for reads, writes, dirty page management, and the adaptive hash index.

MVCC via the undo log is InnoDB’s biggest operational difference from PostgreSQL. The clustered index stays clean. Heap bloat does not accumulate. But long-running transactions shift the maintenance burden from vacuum to undo log growth, and the failure mode is more subtle: instead of tables bloating visibly on disk, you get gradually slower reads as version chains lengthen.

The redo log plus doublewrite buffer represent a carefully balanced durability design: the redo log makes write throughput possible by converting random page writes into sequential log writes, while the doublewrite buffer protects against the specific failure mode of torn writes that the redo log alone cannot recover from.

Understanding these mechanics translates directly into better schema design decisions, more confident production tuning, and faster diagnosis when MySQL behaves unexpectedly under load.