Understanding MySQL REDO Log: Purpose, Types, and Flush Timing

The article begins by emphasizing that REDO logs capture modifications made by logical transactions to the buffer pool pages, providing durability and enabling recovery after crashes.

It then explores the consequences of operating without logs: immediate page flushing, massive I/O, inability to roll back uncommitted changes, and overall performance degradation, illustrating why logs are a core distinction between a DBMS and a simple file system.

Three key performance benefits of logging are highlighted: reducing page‑flush frequency by writing to sequential logs, avoiding unnecessary 16 KB page writes for small changes, and converting random I/O into sequential log I/O, which greatly improves IOPS.

The impact of log file size is discussed, showing how larger log capacities allow many more modifications before a checkpoint forces a page flush, while overly large logs can increase recovery time after a crash; an optimal log‑to‑buffer‑pool size ratio of roughly 5:1–10:1 is recommended.

A detailed enumeration of InnoDB REDO log record types follows, describing each format (e.g., MLOG_1BYTE, MLOG_WRITE_STRING, MLOG_COMP_REC_MIN_MARK, MLOG_UNDO_INSERT, MLOG_INIT_FILE_PAGE, MLOG_COMP_PAGE_CREATE, MLOG_MULTI_REC_END, MLOG_COMP_REC_CLUST_DELETE_MARK, MLOG_COMP_REC_DELETE, MLOG_COMP_PAGE_REORGANIZE, MLOG_COMP_REC_INSERT) and the data they store such as type, tablespace ID, page offset, and record‑specific payload.

Finally, the article outlines five log‑flushing moments in InnoDB: when the log buffer is full, the background master thread flushes every second, proactive flushes during DML when log space is low, checkpoint‑driven flushes to guarantee LSN ordering, and transaction‑commit‑controlled flushes governed by the innodb_flush_log_at_trx_commit setting.