InnoDB Physiological Logging

Updated at:

This topic describes the redo log types of InnoDB.

Physical logging

Physical logging records the changed bytes on a page in a log. The log is in a pure physical format and records data changes byte by byte. For example, a content change is recorded in the [start,end,'xxxx'] format.

  • Advantages

    It is highly efficient, enabling direct modification of the physical format without requiring an operation to re-traverse the B-tree to the specified page.

  • Disadvantages

    A high volume of logs is generated.

    For example, for a DELETE operation, logical logging only needs to record the MLOG_COMP_REC_DELETE offset. However, the actual execution must modify prev_record->next_record, next_record->prev_record, the checksum, and PAGE_DIR_SLOT_MIN_N_OWNED. It may also need to update information, such as the dir slot. With physical logging, this information is located in different places on the page, which requires many log records.

    Physical logging also records a large amount of redundant logs during page reorganization or in Split Merge Operation (SMO) scenarios. For example, a page's structure must be reorganized if it has many deleted records and fragments that need to be cleaned up. With physical logging, all records must be re-inserted into the current page, and their offsets must be re-recorded. In contrast, logical logging only requires you to record MLOG_PAGE_REORGANIZE. For a 16 KB page, logical logging requires only a few bytes, whereas physical logging requires about 16 KB.

Logical logging

Logical logging records operations on a table. This is similar to the binary log (binlog) generated at the upper layer of a MySQL database. It records operations instead of physical formats.

  • Advantages

    Logical logging is very efficient. For example, a DELETE operation requires only a few bytes to be recorded. This is especially apparent in scenarios such as SMO and page reorganization.

  • Disadvantages

    Because logical logging records the record_id, all changes require traversing the B-tree again. The logic must also be modified serially using an index lock. In contrast, physical logging provides the highest fault recovery efficiency. This is because pages have no dependencies on each other, which allows logs to be replayed in parallel.

Physiological logging

Most commercial databases use physical-logical logs. The design philosophy is "physical to a page, logical within a page", where logs are recorded for each physical page, but the entries describe the logical operation. InnoDB uses the same method, combining the advantages of physical and logical logs.

In the InnoDB storage engine, each redo log type has a fixed header format, as shown in the following figure: InnoDB The redo_log_type field defines the type of logical operation. The space and page_no fields indicate the physical page where the log was generated. Therefore, all redo logs in the InnoDB storage engine are physiological logs.

The InnoDB redo log type specifies a logical operation type. Some operation types require record modifications and record an offset. Many operations are logical, such as MLOG_1BYTE, MLOG_2BYTE, and MLOG_INIT_FILE_PAGE.

For operations such as INSERT, UPDATE, and DELETE, changes are recorded at the page level. This allows logs to be replayed in parallel during fault recovery without traversing the B-tree again to find the page, which speeds up the recovery process.

PolarDB has also improved the current InnoDB logs to reduce redundancy. For example, it adds record length information and reduces the number of page ID records in consecutive mini-transactions (mtrs).