Understanding MySQL InnoDB Redo Log Generation, Buffering, and Flushing Mechanisms

In MySQL 8.0 the redo log, which records changes to InnoDB pages for crash recovery, goes through four stages: generation, writing to the log buffer, writing to the redo log file, and flushing to disk.

1. Overview – Earlier versions wrote redo logs to the log buffer serially, requiring a mutex lock. Starting with MySQL 8.0 the write is parallel: multiple user threads can reserve space in the log buffer simultaneously, greatly improving throughput.

2. Redo Log Generation – For a simple INSERT with an auto‑increment column, two redo records are produced: one storing the new maximum auto‑increment value and another storing the row data. These records are grouped into a log group belonging to a Mini‑Transaction ( mtr ). The mtr also holds a temporary m_log chain of 512‑byte blocks where the generated redo records are initially stored.

3. Writing to the Log Buffer – When an mtr commits, it obtains a contiguous sequence number range ( start_sn – end_sn ) based on the current maximum SN and the length of its redo data. This range is then converted to a Log Sequence Number (LSN) range ( start_lsn – end_lsn ) which reserves space in the log buffer. The reservation is lock‑free because each mtr gets its own slice of the buffer. If the buffer lacks enough free space, the thread waits on log.write_events until the log_writer thread frees space.

4. Writing to the Log File – The dedicated log_writer thread moves redo data from the log buffer to the operating‑system file cache. It can only write a contiguous LSN range; gaps caused by out‑of‑order mtr completions are tracked using the global log_sys.recent_written structure. This structure contains an array m_links (default 1,048,576 slots) where each slot stores the end_lsn for the mtr that started at the corresponding start_lsn . The recent_written.m_tail value marks the highest LSN up to which logs are known to be contiguous. The writer scans slots starting from m_tail , updates m_tail when it finds a continuous region, and then advances log_sys.write_lsn . After writing, it notifies waiting threads via log.write_events (or the log_write_notifier thread when many threads wait).

5. Log File Flushing – The log_flusher thread periodically (≈ once per second) flushes the OS file cache to durable storage. It also listens to log.flusher_event , which is triggered by transactions that require immediate durability (e.g., innodb_flush_log_at_trx_commit=1 ). The flusher compares log_sys.write_lsn with log_sys.flushed_to_disk_lsn ; if the former is larger, it performs an fsync and updates flushed_to_disk_lsn . Upon completion it notifies any transaction threads waiting on log.flush_events[slot] (or the log_flush_notifier thread when multiple waiters exist).

6. Summary – Redo logs are produced as log groups within an mtr , written lock‑free to the log buffer using pre‑reserved LSN ranges, and persisted to disk by the log_writer and log_flusher threads. The recent_written structure guarantees that only contiguous log regions are written to the file, while event‑based notifications keep user threads informed about buffer space and durability status.