InnoDB Redo Log Persistence Analysis

Background

Write-ahead logging (WAL) is one of the most important components of a database. It ensures the atomicity and durability of data operations. WAL (referred to as redo log in InnoDB) records all modifications to data pages. All modifications must be written to log files before being committed. This delays the time when modified pages are flushed to disk and prevents data loss.

When the system processes write operations to the redo log under a workload-intensive scenario, performance can be constrained by synchronization overhead among multiple user threads. This is especially evident in scenarios with multiple CPUs and high-performance storage devices.

This section focuses on the redo log persistence process in MySQL 8.0 and describes the optimization points of the redo log and the interaction logic of related threads.

Basic Process

When a user transaction modifies data, a data page modification operation may contain multiple redo logs. Each redo log is assigned a globally unique and incrementing log sequence number (LSN) to identify the sequence of the log record.

Each redo log is atomically committed by a mini-transaction (mtr for short), which is the smallest unit in MySQL for executing data modification operations. When a modification operation is performed on a data page, MySQL creates several mtr transactions to ensure the atomicity of the operation. The generated redo log is temporarily stored in m_log of the mtr, which is a dynamically allocated memory space.

Upon mtr commitment, the system first copies the redo log from m_log to the redo log buffer, updates the LSN position in the log buffer, and then wakes up the background thread to perform the persistence operation based on the innodb_flush_log_at_trx_commit setting.

Figure 1 User thread generating a redo log and waiting for it to be flushed to disk

1. The Log Writer is responsible for writing the content in the redo log buffer to the OS cache.
2. After the write is complete, the Log Writer thread advances the write_lsn and wakes up the Log Flusher and Log Writer Notifier threads.
3. The Log Flusher is responsible for flushing redo logs, which is unrelated to the flushing of dirty data pages. The flushing of dirty data pages is handled by the buffer pool flushing thread. When flushed_to_disk_lsn lags behind write_lsn, the Log Flusher thread executes the fsync() function to flush data from the OS cache to disk, thereby completing the actual flushing of redo logs.
4. After that, the Log Flusher thread advances the flushed_to_disk_lsn and wakes up the Log Flush Notifier thread.
5. The Log Write Notifier or Log Flush Notifier thread wakes up the user thread that is waiting for write_lsn or flushed_to_disk_lsn. MySQL uses the innodb_flush_log_at_trx_commit parameter to control the time when a transaction is committed and determine whether the Log Write Notifier or Log Flush Notifier thread should wake up the user thread.
6. After the user thread is woken up, it compares the commit_lsn of its transaction with write_lsn or flushed_to_disk_lsn.
   • If commit_lsn is less than or equal to write_lsn or flushed_to_disk_lsn, the corresponding redo log has been written to the OS cache or disk, and the transaction can be committed.
   • If commit_lsn is still greater than write_lsn or flushed_to_disk_lsn, the user thread continues to wait for the next wake-up.

Key Threads

Redo log persistence involves four background threads: Log Writer, Log Flusher, Log Write Notifier, and Log Flush Notifier. These four threads are started when InnoDB is started.

• Log Writer

  The Log Writer spins and continuously checks whether there are redo logs to be written to the OS cache. Before writing, it checks whether the current ib_logfile file has sufficient space to accommodate the redo logs to be written.

• Log Flusher

  After increasing the write_lsn, the Log Writer notifies the Log Flusher thread. The Log Flusher thread calls fsync() to flush redo logs to disk and advances the flush_up_to_lsn. The synchronization between the Log Flusher and the Log Writer is performed solely through the write_lsn, which indicates the current write position.

• Log Notifier

  The Log Notifier consists of two key threads: Log Write Notifier and Log Flush Notifier. These two threads periodically poll (or are woken up) to check their respective LSN positions: the Log Write Notifier focuses on the write_lsn, while the Log Flush Notifier focuses on the flush_up_to_lsn. When these LSN values advance to the positions awaited by user threads, they wake the corresponding user threads. During transaction commits, user threads block on the log_write_up_to function and only proceed once the respective write_lsn or flush_up_to_lsn has reached the target position.