Inside MySQL InnoDB: How Redo, Undo, and Buffer Pools Ensure Data Safety

When you run a simple UPDATE, MySQL’s InnoDB engine triggers a complex sequence to keep data safe, consistent, and crash‑recoverable.

mysql> UPDATE testdb.t1 SET col1=1 WHERE id=1;

Log Buffer: First Stop for Changes

Updates are first written to the in‑memory log buffer, not to disk, providing a fast but temporary store.

Shortly after, InnoDB flushes these changes to redo logs (files such as #ib_redo0, #ib_redo1). Redo logs are essential for crash recovery because they allow MySQL to replay the last consistent state.

Buffer Pool and Dirty Pages: The Real Data

The actual table pages reside in the buffer pool. After an update, the modified pages become dirty pages, meaning they contain changes not yet written to disk.

Dirty pages accumulate in the lower‑left part of the diagram and are flushed to disk by the background flush thread, keeping the pool healthy and reducing recovery time.

Undo Log and Undo Segments

For every UPDATE or DELETE, InnoDB records the old values in an undo log. The undo log serves two purposes:

Rollback : If a transaction issues ROLLBACK, InnoDB uses the undo log to restore the previous row state.

MVCC : The undo log lets concurrent transactions see a consistent snapshot; a transaction may still read the old value of col1=1 depending on its isolation level.

Undo records are stored in undo segments, which are grouped inside rollback segments, allowing multiple transactions to maintain their own undo information safely.

When old versions are no longer needed, a background purge thread removes undo records, preventing tablespace bloat.

Redo Log Checkpoints

Redo logs are finite. When they fill up, InnoDB must flush dirty pages to free space. The diagram’s right side shows four thresholds that control flushing:

Aggressive checkpoint (31/32) : Flushes as fast as possible when space is exhausted.

Synchronous flush point (15/16) : InnoDB blocks other transactions to flush pages, noticeably impacting performance.

Asynchronous flush point (7/8) : Pages are flushed in the background while writes continue, possibly increasing latency.

Adaptive LWM (~10%) : Gradual flushing that controls redo usage without harming performance.

Eventually dirty pages are written from the buffer pool to the actual tablespace files.

Binary Log Entry

When a transaction is ready to commit, InnoDB also writes statement or row events to the binary log, a separate file used for replication and point‑in‑time recovery.

Replication : Replica servers read the binary log to stay in sync.

PITR : Allows restoring a backup and replaying only recent changes.

Flush Timing – innodb_flush_log_at_trx_commit

The variable innodb_flush_log_at_trx_commit coordinates when the log buffer is flushed to the redo log on disk, balancing durability and performance:

0 – Flush once per second (fast, less durable).

1 (default) – Flush on every commit (most durable, slower).

2 – Write on every commit, flush once per second (middle ground).

Setting it to 1 provides true ACID guarantees.

Key Takeaways

Redo Log is more than a crash‑recovery tool; it enables fast sequential logging, concurrent flushing, and delayed dirty‑page writes, improving write performance.

Buffer Pool holds actual data pages; flushing them reduces memory pressure.

Binary Log is separate and required for replication and PITR; it does not duplicate redo functionality.

innodb_flush_log_at_trx_commit determines whether commits are truly persistent; choose wisely.

Monitoring redo‑log usage and sizing it appropriately prevents MySQL from stalling or blocking writes.