MySQL / InnoDB Internals

How MySQL's InnoDB engine actually writes, versions, and replicates rows underneath a client connection — the buffer pool, the redo/undo logs that make crashes and rollbacks safe, and the binlog+GTID pipeline that keeps replicas in sync.

InnoDB Storage Engine Architecture

graph TD
    classDef client fill:#34495e,stroke:#212f3c,color:#fff
    classDef mem fill:#3498db,stroke:#2471a3,color:#fff
    classDef durable fill:#e67e22,stroke:#ba6018,color:#fff
    classDef disk fill:#7f8c8d,stroke:#616a6b,color:#fff

    CLIENT2["Client query"]:::client --> PARSER["SQL Parser + Optimizer<br/>picks the execution plan"]:::client
    PARSER --> EXEC["Execution Engine<br/>row-by-row iterator over the plan"]:::client
    EXEC --> INNODB["InnoDB Storage Engine"]

    subgraph INNODB["InnoDB"]
        BP2["Buffer Pool (innodb_buffer_pool_size)<br/>data + index + undo pages<br/>LRU with young/old sublists"]:::mem
        CHANGE["Change Buffer<br/>buffers secondary-index changes<br/>when the index page isn't cached"]:::mem
        REDO["Redo Log (ib_logfile0, ib_logfile1)<br/>WAL for crash recovery<br/>circular buffer, fixed size"]:::durable
        UNDO["Undo Log (ibdata1 / undo tablespace)<br/>old row versions for MVCC<br/>+ rollback segments"]:::durable
    end

    EXEC -->|"row read/write"| BP2
    EXEC -->|"before-image write"| UNDO
    EXEC -->|"redo record on modify"| REDO
    CHANGE -.->|"merged in on page read<br/>or by the purge thread"| BP2
    BP2 -->|"checkpoint: flush dirty pages"| DATA2["Data files (.ibd)<br/>clustered B-tree on primary key<br/>all data in leaf nodes"]:::disk
    REDO -.->|"replayed on crash recovery<br/>if newer than last checkpoint"| DATA2

The buffer pool is where reads and writes actually happen — data and index pages live there entirely in memory, and a query only touches disk when a page isn't cached. Redo and undo solve two different problems that both start with that same modified page: redo makes the change durable before the dirty page is ever flushed to the .ibd file, while undo keeps the previous row version around for both rollback and any transaction still reading an older MVCC snapshot. The change buffer exists purely to avoid random-I/O stalls: if a secondary-index page a write needs to touch isn't in the buffer pool, InnoDB records the change there instead of paging the index block in immediately, merging it in later when the page is naturally read or the purge thread gets to it.

The diagram labels the eviction policy "LRU with young/old sublists" almost in passing, but that's not the same structure as the plain LRU cache walked through in coding-practice/lru-cache.md — the difference is deliberate, not incidental. A single doubly-linked-list LRU has a real bug for a database's most common workload: a one-off SELECT * FROM huge_table full scan reads millions of pages exactly once, and a plain LRU would shove every single one of those reads straight to the MRU end, evicting whatever was genuinely hot from repeated real queries in the process — one scan, entire working set gone. InnoDB's fix is to split the LRU list into two sublists instead of one flat list: an OLD sublist (innodb_old_blocks_pct, default 37% — roughly 3/8 of the pool) and a YOUNG sublist (the remaining ~5/8). A newly read page always lands at the head of the OLD sublist, never the young one, so a scan floods and churns the old sublist while never touching a page that's already proven itself with a second access. Only a repeat access promotes a page from OLD to YOUNG. (Real InnoDB also makes a page wait innodb_old_blocks_time milliseconds before that promotion counts, specifically to stop a tight scan loop from re-reading the same page twice in a row and promoting it by accident — the demo below skips that timer for simplicity and just promotes on the second distinct access.)

Redo Log (WAL) + Undo Log

sequenceDiagram
    participant TX2 as Transaction
    participant BP3 as Buffer Pool
    participant REDO2 as Redo Log
    participant UNDO2 as Undo Log
    participant DISK2 as Data files

    rect rgba(230, 126, 34, 0.15)
    Note over TX2,UNDO2: Before commit — durability boundary not yet crossed
    TX2->>UNDO2: Write before-image (rollback + MVCC snapshot)
    TX2->>BP3: Modify page in buffer pool (now dirty)
    TX2->>REDO2: Write redo record (new value)
    end
    Note over REDO2: fsync on COMMIT if innodb_flush_log_at_trx_commit=1
    TX2-->>TX2: COMMIT confirmed to client

    Note over BP3,DISK2: Later, asynchronously — decoupled from commit latency
    BP3->>DISK2: Checkpoint flushes dirty pages
    Note over REDO2: redo entries before the checkpoint can now be overwritten

    rect rgba(231, 76, 60, 0.15)
    Note over DISK2,REDO2: Crash before the next checkpoint
    DISK2->>REDO2: On restart, InnoDB scans redo log from last checkpoint
    REDO2->>DISK2: Replays every committed redo record forward
    Note over DISK2: Data files caught up to the last fsynced commit —<br/>nothing acknowledged to a client is lost
    end

The redo log is what makes a commit durable long before the modified page is flushed to disk — the buffer pool page and the on-disk data file can lag behind indefinitely, because the redo log is exactly the fallback InnoDB uses to reconstruct that gap after a crash.

innodb_flush_log_at_trx_commit:

MVCC with Undo Log

graph TD
    classDef live fill:#e74c3c,stroke:#c0392b,color:#fff
    classDef undo fill:#e67e22,stroke:#ba6018,color:#fff
    classDef txold fill:#3498db,stroke:#2471a3,color:#fff
    classDef txnew fill:#27ae60,stroke:#1e8449,color:#fff

    CURRENT["Current row version (in the table)<br/>id=1, name='Bob', trx_id=200"]:::live
    CURRENT -->|"roll pointer"| PREV["Undo log v1<br/>id=1, name='Alice', trx_id=100"]:::undo
    PREV -->|"roll pointer"| PREV2["Undo log v0<br/>id=1, name='Alex', trx_id=50"]:::undo

    TX_OLD["Old transaction<br/>read view taken before trx 200 committed"]:::txold
    TX_NEW["New transaction<br/>read view taken after trx 200 committed"]:::txnew

    TX_OLD -.->|"trx_id 200 not visible yet →<br/>follow roll pointer"| PREV
    TX_NEW -->|"trx_id 200 already visible →<br/>read current row directly"| CURRENT

Unlike PostgreSQL (which stores old versions in the heap), MySQL/InnoDB stores them in undo logs.

Undo log purge: Background purge thread removes undo records no longer needed by any transaction. Long-running transactions prevent purge → undo tablespace grows.

InnoDB B-tree (Clustered Index)

InnoDB's primary key is the clustered index — every row lives directly in the leaf nodes of that B-tree, there's no separate heap the index points into. A secondary index's leaf nodes don't store the row at all; they store the primary key value, which means looking up a row through a secondary index costs a second lookup into the clustered index — the "index dive."

graph LR
    classDef query fill:#34495e,stroke:#212f3c,color:#fff
    classDef clustered fill:#2980b9,stroke:#1f618d,color:#fff
    classDef secondary fill:#8e44ad,stroke:#6c3483,color:#fff
    classDef result fill:#27ae60,stroke:#1e8449,color:#fff

    Q1["SELECT * WHERE id=123"]:::query --> CIDX["Clustered index B-tree<br/>keyed on PRIMARY KEY (id)"]:::clustered
    CIDX -->|"1 lookup"| LEAF1["Leaf node<br/>= the entire row"]:::result

    Q2["SELECT * WHERE email='alice@example.com'"]:::query --> SIDX["Secondary index B-tree<br/>keyed on email"]:::secondary
    SIDX -->|"1st lookup"| LEAF2["Leaf node<br/>= PK value only (id=123)"]:::secondary
    LEAF2 -->|"2nd lookup — the 'index dive'"| CIDX2["Clustered index B-tree<br/>keyed on PRIMARY KEY (id)"]:::clustered
    CIDX2 --> LEAF3["Leaf node<br/>= the entire row"]:::result

Replication: Binary Log (Binlog)

sequenceDiagram
    participant SRC as Source (Primary)
    participant BINLOG as Binary Log
    participant IO_THREAD as IO Thread (replica)
    participant RELAY as Relay Log (replica)
    participant SQL_THREAD as SQL Thread (replica)
    participant REP_DB as Replica DB

    rect rgba(52, 152, 219, 0.12)
    Note over SRC,BINLOG: On commit
    SRC->>SRC: Transaction commits, assigned a GTID<br/>(source_uuid:transaction_number)
    SRC->>BINLOG: Write binlog event tagged with that GTID
    end

    loop continuous, asynchronous streaming
        IO_THREAD->>BINLOG: Request next event after last-read position
        BINLOG-->>IO_THREAD: Stream new binlog events
        IO_THREAD->>RELAY: Append to relay log
    end

    loop continuous apply
        SQL_THREAD->>RELAY: Read next relay log event
        SQL_THREAD->>SQL_THREAD: Check gtid_executed — skip if<br/>this GTID was already applied
        SQL_THREAD->>REP_DB: Execute SQL / apply row image
        SQL_THREAD->>SQL_THREAD: Record GTID in gtid_executed
    end

    Note over SRC,REP_DB: Replica tracks position purely by GTID —<br/>no binlog filename+offset bookkeeping needed

Binlog formats:

• STATEMENT: log SQL statements (compact, but non-deterministic queries can diverge)
• ROW: log actual row changes (verbose, always correct — used by default now)
• MIXED: statement for safe queries, row for non-deterministic

GTID (Global Transaction ID): Each transaction gets a globally unique ID. Replicas use GTIDs to track position — no need to know binlog filename + offset. Enables automatic failover.

Key Configuration

innodb_buffer_pool_size = 12G      # 70-80% of RAM
innodb_buffer_pool_instances = 8   # reduce contention
innodb_log_file_size = 2G          # larger = faster writes, slower crash recovery
innodb_flush_log_at_trx_commit = 1 # 1 = full durability (use 2 for replicas)
innodb_flush_method = O_DIRECT     # bypass OS cache (avoid double-buffering)
sync_binlog = 1                    # sync binlog to disk per transaction
binlog_format = ROW
gtid_mode = ON
enforce_gtid_consistency = ON

Slow Query Log and Analysis

graph LR
    classDef source fill:#3498db,stroke:#2471a3,color:#fff
    classDef tool fill:#8e44ad,stroke:#6c3483,color:#fff
    classDef report fill:#f39c12,stroke:#ba6018,color:#fff
    classDef fix fill:#27ae60,stroke:#1e8449,color:#fff

    subgraph SOURCES["Detection sources"]
        SQ["Slow Query Log<br/>file — queries over long_query_time"]:::source
        PS["Performance Schema<br/>in-memory digest table, no file I/O"]:::source
    end

    SQ -->|"offline analysis"| PT["pt-query-digest<br/>(Percona Toolkit)"]:::tool
    PT --> REPORT["Digest report<br/>ranked by total time / count / avg time"]:::report
    PS -->|"live query, no log file needed"| REPORT

    REPORT --> FIX["Add index / rewrite query<br/>/ partition table"]:::fix

The slow query log and Performance Schema are two independent ways to catch the same problem: the log needs slow_query_log turned on and writes to a file you analyze after the fact, while events_statements_summary_by_digest is always accumulating in memory and can be queried live with no configuration flag or file I/O at all — either path should converge on the same fix.

# Enable slow query log
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1           # log queries > 1 second
log_queries_not_using_indexes = ON  # catch full table scans
min_examined_row_limit = 1000 # skip trivially small queries

# Analyze slow query log
pt-query-digest /var/log/mysql/slow.log | head -100

# Output shows per-query fingerprint:
# Query 1: 23.45s total, 234 calls, 0.10s avg
# SELECT * FROM orders WHERE user_id = ? AND status = ?
# Rows examined: 50000 → index missing on (user_id, status)

-- Find slow queries in real time (Performance Schema)
SELECT DIGEST_TEXT, COUNT_STAR, AVG_TIMER_WAIT/1e12 AS avg_sec,
       SUM_ROWS_EXAMINED/COUNT_STAR AS avg_rows_examined
FROM performance_schema.events_statements_summary_by_digest
WHERE AVG_TIMER_WAIT > 1e12  -- > 1 second
ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;

Gap Locks and Next-Key Locking (REPEATABLE READ)

InnoDB's default isolation level is REPEATABLE READ, and the SQL standard's own definition of that level only promises one thing: a row you already read won't appear to change if you read it again in the same transaction. It says nothing about rows that don't exist yet. Left at that, a range query like SELECT * FROM t WHERE id BETWEEN 10 AND 20 FOR UPDATE would only be able to lock the rows it actually found — id=10 and id=20 — and a plain row lock on those two rows does nothing to stop a completely different transaction from inserting a brand-new row, id=15, into the gap between them before this transaction commits. That's a phantom read: re-run the same range query and a row appears that wasn't there a moment ago, even though every row you originally locked is untouched. InnoDB closes that hole itself, beyond what the standard requires, using two lock types that don't exist in the row-lock model alone:

• Gap lock — locks the empty space between two consecutive index records (or before the first record / after the last), with no lock on any actual row. Its only job is to block another transaction from inserting a new index entry into that space.
• Next-key lock — a record lock on an existing index entry combined with a gap lock on the space immediately before it. This, not a plain record lock, is what a range scan under REPEATABLE READ actually takes by default — every index record InnoDB examines during the scan gets locked together with the gap leading up to it.

graph LR
    classDef locked fill:#8e44ad,stroke:#6c3483,color:#fff
    classDef record fill:#e74c3c,stroke:#c0392b,color:#fff
    classDef blocked fill:#c0392b,stroke:#922b21,color:#fff

    subgraph NK1["Next-key lock on id=10"]
        G0["gap: -inf .. 10"]:::locked
        R10["record id=10"]:::record
    end

    subgraph NK2["Next-key lock on id=20"]
        G1["gap: 10 .. 20"]:::locked
        R20["record id=20"]:::record
    end

    G0 --> R10 --> G1 --> R20

    INS["INSERT id=15"]:::blocked -.->|"falls inside the locked gap<br/>BLOCKED until T1 commits/rolls back"| G1

With rows already existing at id=10 and id=20, the range query locks two next-key locks: one on record 10 covering the gap before it, and one on record 20 covering the gap between 10 and 20. id=15 falls inside that second gap — nothing about it involves the rows 10 or 20 at all, but the gap they bracket is locked all the same.

This is precisely why gap-lock blocking catches developers off guard when they're used to another database's READ COMMITTED-style semantics (or MySQL's own READ COMMITTED): an INSERT that looks completely unrelated to a concurrent SELECT ... FOR UPDATE — different id, no row in common — can still sit in a lock wait, and SHOW ENGINE INNODB STATUS\G will show it waiting on a gap, not a row. That's a common, confusing source of production lock-wait timeouts that look like they "shouldn't" be possible.

The fixed walkthrough above always uses the same table (rows at 10 and 20) and the same blocked insert (15) — useful for seeing the mechanism once, but it can't show you where the gap boundaries actually fall for a table shape of your own choosing. The live version below runs the exact same rule (a range scan takes next-key locks bounded by whichever real rows sit on either side of the queried range, not clipped to the range's own start/end) against whatever rows, range, and insert ids you type in, and lets you flip the isolation toggle on a lock you're already holding to watch the gap locks disappear.

Deadlock Analysis

sequenceDiagram
    participant TX1 as Transaction 1
    participant TX2 as Transaction 2
    participant ROW_A as Row A
    participant ROW_B as Row B

    TX1->>ROW_A: SELECT ... FOR UPDATE (lock A)
    activate ROW_A
    TX2->>ROW_B: SELECT ... FOR UPDATE (lock B)
    activate ROW_B

    rect rgba(231, 76, 60, 0.15)
    TX1->>ROW_B: SELECT ... FOR UPDATE (waiting for B)
    TX2->>ROW_A: SELECT ... FOR UPDATE (waiting for A)
    Note over TX1,TX2: Circular wait — DEADLOCK.<br/>InnoDB's lock-wait-for graph detects the cycle
    end

    Note over TX2: InnoDB picks TX2 as victim<br/>(estimated less rollback work to undo)
    TX2-->>TX2: ERROR 1213: Deadlock — rolled back, must retry
    deactivate ROW_B
    TX1->>ROW_B: Lock acquired, continues
    deactivate ROW_A

InnoDB doesn't pick the victim by who detected the deadlock, or by who started first — it estimates which transaction has done less work to undo (fewer rows modified so far) and kills that one, since rolling back a shorter transaction is cheaper than rolling back a longer one.

-- View last deadlock
SHOW ENGINE INNODB STATUS\G
-- Look for "LATEST DETECTED DEADLOCK" section
-- Shows: which transactions, which rows were locked, who was killed

-- Enable deadlock logging
innodb_print_all_deadlocks = ON  -- logs every deadlock to error log

-- Prevent deadlocks: always lock rows in the same order
-- Bad: TX1 locks A then B, TX2 locks B then A
-- Good: both always lock in alphabetical/ID order

EXPLAIN and Index Optimization

-- Full EXPLAIN output
EXPLAIN FORMAT=JSON SELECT u.name, COUNT(o.id)
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.created_at > '2024-01-01'
GROUP BY u.id\G

-- Key fields to check:
-- type: ALL (full scan bad), ref/eq_ref (good), range (ok), index (scan index only)
-- key: NULL means no index used
-- rows: estimated rows to examine
-- Extra: "Using filesort" or "Using temporary" = expensive

-- Find missing indexes
EXPLAIN SELECT * FROM orders WHERE user_id = 123 AND status = 'pending'\G
-- If type=ALL and rows=100000 → add composite index

CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- Covering index: index contains all columns the query needs (no table lookup)
CREATE INDEX idx_orders_covering ON orders(user_id, status, created_at, amount);
-- Query: SELECT amount FROM orders WHERE user_id=1 AND status='paid'
-- Extra: "Using index" → reads index only, never touches table rows

Partitioning

-- Range partitioning by year (for large time-series tables)
CREATE TABLE orders (
    id BIGINT NOT NULL,
    user_id BIGINT,
    amount DECIMAL(10,2),
    created_at DATETIME NOT NULL
)
PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION pmax  VALUES LESS THAN MAXVALUE
);

-- Query uses partition pruning automatically
EXPLAIN SELECT * FROM orders WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';
-- partitions: p2024  ← only scans 2024 partition, skips 2022-2023

-- Drop old partition instantly (no row-by-row DELETE)
ALTER TABLE orders DROP PARTITION p2022;  -- instant, reclaims disk space

-- List partitions and row counts
SELECT PARTITION_NAME, TABLE_ROWS, DATA_LENGTH/1024/1024 AS data_mb
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'orders';

Partition pruning happens purely from the WHERE clause's relationship to the partitioning expression — MySQL doesn't need to touch an index to decide which partitions to skip, it decides that from the partition definition itself, and only then applies whatever indexing exists inside the partitions it does scan.

Performance Schema — Real-Time Diagnostics

-- Top wait events (where time is spent)
SELECT EVENT_NAME, COUNT_STAR, SUM_TIMER_WAIT/1e12 AS total_sec
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE COUNT_STAR > 0
ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;

-- I/O by table
SELECT OBJECT_SCHEMA, OBJECT_NAME,
       COUNT_READ, SUM_TIMER_READ/1e12 AS read_sec,
       COUNT_WRITE, SUM_TIMER_WRITE/1e12 AS write_sec
FROM performance_schema.table_io_waits_summary_by_table
ORDER BY (SUM_TIMER_READ + SUM_TIMER_WRITE) DESC LIMIT 10;

-- Current connections with their last query
SELECT PROCESSLIST_ID, PROCESSLIST_USER, PROCESSLIST_HOST,
       PROCESSLIST_DB, PROCESSLIST_COMMAND, PROCESSLIST_TIME,
       LEFT(PROCESSLIST_INFO, 100) AS query
FROM performance_schema.processlist
WHERE PROCESSLIST_COMMAND != 'Sleep'
ORDER BY PROCESSLIST_TIME DESC;

Key Monitoring Queries

-- Buffer pool hit rate (target > 99%)
SELECT (1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests) * 100 AS hit_rate_pct
FROM (
    SELECT variable_value AS Innodb_buffer_pool_reads FROM information_schema.global_status WHERE variable_name = 'Innodb_buffer_pool_reads'
) r, (
    SELECT variable_value AS Innodb_buffer_pool_read_requests FROM information_schema.global_status WHERE variable_name = 'Innodb_buffer_pool_read_requests'
) rr;

-- Replication lag in seconds
SHOW REPLICA STATUS\G
-- Seconds_Behind_Source: 0 = in sync, >30 = alert

-- InnoDB row lock waits (high = contention)
SHOW STATUS LIKE 'Innodb_row_lock%';
-- Innodb_row_lock_waits: cumulative waits
-- Innodb_row_lock_time_avg: average wait ms

-- Table sizes
SELECT table_name,
       round(data_length/1024/1024, 1) AS data_mb,
       round(index_length/1024/1024, 1) AS index_mb,
       table_rows
FROM information_schema.tables
WHERE table_schema = DATABASE()
ORDER BY data_length + index_length DESC LIMIT 20;