Durability: redo, undo, doublewrite, recovery

The problem: a transaction modifies pages scattered all over a 200GB file. Writing them all to disk at commit would mean a dozen random writes and a latency of tens of milliseconds. Not writing them at all means losing them in a crash.

The answer is write-ahead logging. Do not write the pages. Write a compact, sequential description of the change to a log, and fsync only that. The modified pages stay dirty in memory and get written later, lazily, in bulk.

The LSN: a clock made of bytes

The Log Sequence Number is a monotonically increasing count of bytes ever written to the redo log. It is not a timestamp and not a counter of transactions — it is a byte offset that only goes up.

That makes it a universal ordering for everything in the engine. Several LSNs exist at once, and their relationship is the state of the system:

-- The LOG section of this output is the whole story.
SHOW ENGINE INNODB STATUS\G

--   Log sequence number   ← current LSN, in memory
--   Log flushed up to     ← durable on disk
--   Pages flushed up to   ← oldest dirty page
--   Last checkpoint at    ← recovery would start here

-- The same values, queryable:
SELECT name, count FROM information_schema.innodb_metrics
WHERE name IN ('log_lsn_current', 'log_lsn_last_flush',
                'log_lsn_last_checkpoint', 'log_lsn_checkpoint_age');

-- checkpoint_age is the number that matters. If it approaches
-- innodb_redo_log_capacity, the server will stall (chapter 73).

A set of fixed-size files used as one circular buffer. Writing proceeds to the end, then wraps to the beginning. Space can only be reused once a checkpoint confirms every page it describes has been written to disk.

What a redo record contains

Redo is physiological — physical at the page level, logical within it. Not "the row now reads X", and not the whole 16KB page, but "at page (space 421, page 87), offset 1204, apply this small change."

-- conceptually
{ type: MLOG_REC_INSERT,
  space_id: 421, page_no: 87,
  offset: 1204,
  data: <the record bytes> }

This is why redo is small and fast to write: an insert of a 200-byte row costs roughly 200 bytes of log, not 16KB of page.

Mini-transactions

Internally, InnoDB groups redo records into mini-transactions (mtr). A single page split touches several pages — the split page, the new page, the parent, the neighbour's linked-list pointers — and all of those must be applied together or not at all during recovery. The mtr is that atomic unit. It is a lower level than your SQL transaction: one INSERT may produce several mtrs.

Sizing

Before 8.0.30 you set innodb_log_file_size × innodb_log_files_in_group, and changing it required a clean shutdown. Since 8.0.30 there is a single dynamic innodb_redo_log_capacity you can change at runtime.

-- 8.0.30+: one dynamic setting.
SELECT @@innodb_redo_log_capacity;
-- SET GLOBAL innodb_redo_log_capacity = 4294967296;   -- 4GB, no restart

-- Is it big enough? Measure how fast you burn through it.
-- Run twice, 60 seconds apart, and diff:
SHOW GLOBAL STATUS LIKE 'Innodb_os_log_written';

--   bytes_per_hour = (delta / 60) * 3600
--   you want capacity to hold at LEAST an hour of writes;
--   less than that and checkpointing becomes aggressive.

-- Are you already stalling? Any non-zero value here is bad.
SELECT name, count FROM information_schema.innodb_metrics
WHERE name LIKE 'log_waits' OR name LIKE 'innodb_log_waits';
SHOW GLOBAL STATUS LIKE 'Innodb_log_waits';

The most consequential one-line setting in MySQL. It decides what COMMIT actually promises.

The distinction between 0 and 2 is the one people miss. With 2, if mysqld crashes but the machine keeps running, the OS still flushes its page cache and you lose nothing. With 0, a mysqld crash loses the last second, because the data never left MySQL's own buffer.

SELECT @@innodb_flush_log_at_trx_commit, @@sync_binlog;

-- sync_binlog is the SAME dial for the binary log, and both
-- must be 1 for full durability. sync_binlog=1 + flush=1 is
-- often called "the double-one" configuration.

-- Benchmark the difference on YOUR hardware — the gap is
-- entirely determined by your disk's fsync latency:
--   sysbench oltp_write_only --mysql-db=test --tables=4 \
--     --table-size=100000 --threads=16 --time=60 run
-- run once with 1, once with 2, compare TPS.

-- On NVMe the gap is often smaller than people assume.
-- Measure before trading away durability.

If every commit required its own fsync, throughput would cap at roughly the disk's fsync rate — a few thousand per second even on good NVMe, far fewer on spinning disks. Group commit removes that ceiling: while one fsync is in flight, arriving commits queue, and a single subsequent fsync makes all of them durable at once.

Because there are two logs (chapter 90), MySQL runs a three-stage pipeline. Each stage has a leader that acts for the whole group:

1. Flush — write each transaction's events into the binlog buffer.
2. Sync — one fsync of the binlog for the entire group.
3. Commit — tell InnoDB to commit each transaction, in binlog order.

Two settings deliberately add latency to make groups bigger: binlog_group_commit_sync_delay (microseconds to wait) and binlog_group_commit_sync_no_delay_count (stop waiting once this many have queued). On a high-concurrency server, a delay of a few hundred microseconds can increase throughput substantially — each individual commit is slower, but far more of them complete per second.

-- Average group size = commits / actual fsyncs.
-- If this is near 1, every commit is paying its own fsync.
SHOW GLOBAL STATUS WHERE Variable_name IN
  ('Binlog_commits', 'Binlog_group_commits');

-- ratio = Binlog_commits / Binlog_group_commits
--   ~1    → no grouping happening; consider a sync delay
--   10-50 → healthy batching under load

SELECT @@binlog_group_commit_sync_delay,
       @@binlog_group_commit_sync_no_delay_count;

-- Try 100-1000 microseconds on a write-heavy server and
-- measure TPS both ways. It is counter-intuitive but real.

InnoDB's page is 16KB. A disk's atomic write unit is 512 bytes or 4KB. So a 16KB page write is really four or more separate sector writes, and a power failure between them leaves a torn page: half new, half old.

Redo cannot repair that. Redo records say "apply this change at this offset", which assumes the page is a valid starting point. Applying a delta to a corrupt page produces corruption.

The cost is not 2× IO, despite writing everything twice. The doublewrite area is written sequentially in large batches, which is cheap; only the second write is random. Measured overhead is typically 5–10%.

Durability is a chain, and every link can break it:

InnoDB log buffer        -- lost on mysqld crash
   ↓ write()
OS page cache            -- lost on power failure / kernel panic
   ↓ fsync()
disk write cache         -- lost on power failure IF volatile
   ↓ FUA / cache flush
physical media           -- actually durable

fsync() is supposed to push all the way down. Historically, plenty of consumer drives returned success from a cache flush without having done one, because it made their benchmarks look good. This is rarer now but the principle stands: durability is only as good as the least honest component.

innodb_flush_method

SELECT @@innodb_flush_method, @@innodb_doublewrite,
       @@innodb_flush_log_at_trx_commit, @@sync_binlog;

# In a shell — is the drive's write cache volatile?
#   lsblk -D
#   sudo hdparm -W /dev/sda          # 1 = write cache on
#   cat /sys/block/sda/queue/write_cache

# Measure real fsync latency. This is the number that
# determines your commit ceiling:
#   sudo pg_test_fsync -f /var/lib/mysql/testfile
# (postgres tool, but it measures the disk, not the database)

# The only honest durability test: write, then cut power.
# Nobody does this. It is why "we are ACID" is usually untested.

On startup InnoDB compares the checkpoint LSN with the end of the redo log. If they differ, the server did not shut down cleanly and recovery runs.

The counter-intuitive part is that the redo phase replays everything, including changes from transactions that never committed. That is deliberate: redo's job is to reconstruct the exact page state at the moment of the crash, uncommitted changes included. Undo then removes them in a second pass.

Recovery is also idempotent. Every page carries the LSN of its last modification, so a redo record whose LSN is already ≤ the page's LSN is skipped — which is why a crash during recovery is survivable.

When recovery itself crashes, this setting disables progressively more of the engine to get the server up long enough to dump your data. Each level includes everything below it.

# 0. FIRST — copy the data directory. Always. Before anything.
sudo systemctl stop mysql
sudo cp -a /var/lib/mysql /var/lib/mysql.broken.$(date +%F)

# 1. Try level 1.
sudo tee -a /etc/mysql/my.cnf <<'EOF'
[mysqld]
innodb_force_recovery = 1
EOF
sudo systemctl start mysql

# 2. If it starts, dump IMMEDIATELY — do not investigate first.
mysqldump --all-databases --single-transaction \
          --routines --triggers --events > /backup/rescue.sql

# 3. If it did not start, raise to 2, then 3... up to 6.
#    Find out WHY in the error log as you go:
sudo tail -100 /var/log/mysql/error.log

# 4. Rebuild clean, then reload:
#    - stop mysql, move the data dir aside
#    - mysqld --initialize
#    - remove innodb_force_recovery from my.cnf
#    - start, then: mysql < /backup/rescue.sql
#    - replay binlogs for anything after the dump (ch 103)

Durability explains what survives. Part 7 explains what lives in memory in the meantime — the buffer pool, the LRU that decides what stays, and the flushing that keeps checkpoint age under control.