Understanding InnoDB REDO Log Management and MTR Physical Transactions

Wang Zhufeng, a database expert at Qunar.com, specializes in MySQL source code research, database development, management, and automation. He is the author of the open‑source Inception project and the book “MySQL Operations Reference”.

The InnoDB REDO log is used for crash recovery and records modifications to InnoDB tables. By default MySQL creates two log files (ib_logfile0 and ib_logfile1) that are treated as a circular buffer; if they are missing, InnoDB recreates them according to configuration.

Each log entry is identified by a Log Sequence Number (LSN), an 8‑byte monotonically increasing value that grows with every mini‑transaction (MTR) write. The LSN provides both logical and physical positioning of log records.

InnoDB groups log files into a single log group; all files in the group have equal size. Prior to MySQL 5.6.3 the total group size was limited to ~4 GB, while newer versions allow up to 512 GB.

Log files are logically viewed as one continuous file; pages are 512 bytes, matching historic disk block size, which enables sequential writes and improves throughput.

The layout of a log page consists of a 12‑byte header followed by log records. Important fields include LOG_BLOCK_HDR_NO (4 bytes, block number related to LSN), LOG_BLOCK_HDR_DATA_LEN (2 bytes, length of stored log data), LOG_BLOCK_FIRST_REC_GROUP (2 bytes, start position of an MTR), and LOG_BLOCK_CHECKPOINT_NO (4 bytes, checkpoint identifier).

MTR (mini‑transaction) is the fundamental physical‑transaction unit in InnoDB. Its structure is defined as: struct mtr_struct { /* memo stack for locks etc. */ dyn_array_t memo; /* start lsn of the possible logs entry for this mtr */ ib_uint64_t start_lsn; /* end lsn of the possible logs entry for this mtr */ ib_uint64_t end_lsn; /* mini-transaction logs */ dyn_array_t log; /* count of how many page initial logs records have been written to the mtr logs */ ulint n_log_recs; /* log mode */ ulint log_mode; };

The members have the following meaning: memo stores the pages accessed (and locked) by the MTR; log stores the REDO log records generated; n_log_recs counts log records; log_mode indicates whether logging is enabled (e.g., MTR_LOG_ALL or MTR_LOG_NONE); start_lsn and end_lsn record the LSN before and after the transaction.

When a page is fetched into the buffer pool, an MTR must be started (or an existing one used). The page is locked in shared (S) mode for reads or exclusive (X) mode for writes, and the page descriptor is added to memo .

During a write, if the MTR’s log mode includes logging, the change is recorded in the log array and n_log_recs is incremented.

Commit of an MTR writes all its log records to the InnoDB log buffer, which is later flushed to the log files by the master thread. The LSN of the last flushed log (Log flushed up to) represents the point up to which the database can be recovered.

If the log buffer becomes full, its contents are flushed to the log files; the circular nature of the files requires a checkpoint mechanism to free space. A checkpoint writes dirty pages from the buffer pool to disk, making the corresponding log records obsolete.

The checkpoint algorithm selects pages with the smallest LSN values, flushes them, and advances the checkpoint LSN, allowing the freed log space to be reused without compromising consistency.

In summary, REDO logs transform random page writes into sequential log writes, MTRs guarantee atomic physical modifications, and checkpoints coordinate log reuse and data durability in InnoDB.