InnoDB Redo Log

Directly editing or moving the redo logs can cause corruption, and should never normally be attempted.

Overview

The InnoDB storage engine in MariaDB Enterprise Server employs a Redo Log to ensure data is written to disk reliably, even in the event of a crash. This is achieved by the Redo Log acting as a transaction log.

Redo Log records are uniquely identified by a Log Sequence Number (LSN). The Redo Log consists of circular log files of a fixed size, where older records are routinely overwritten by newer ones.

InnoDB periodically performs checkpoints. During a checkpoint operation, InnoDB writes (flushes) the Redo Log records to the InnoDB tablespace files.

In the event of a server crash, InnoDB utilizes the Redo Log for crash recovery during the subsequent server startup. It locates the last checkpoint within the Redo Log and then replays the Redo Log records generated since that checkpoint, effectively flushing these pending changes to the InnoDB tablespace files.

The redo log files are typically named ib_logfileN, where N is an integer. However, starting with , there is a single redo log file, consistently named ib_logfile0. The location of these redo log files is determined by the innodb_log_group_home_dir system variable, if configured. If this variable is not set, the redo log files are created in the directory specified by the datadir system variable. The redo log plays a crucial role during crash recovery and in the background process of flushing transactions to the tablespaces.

Feature Summary

Basic Configuration

Flushing Effects on Performance and Consistency

The system variable determines how often the transactions are flushed to the redo log, and it is important to achieve a good balance between speed and reliability.

Binary Log Group Commit and Redo Log Flushing

When both (the default) is set and the is enabled, there is one less sync to disk inside InnoDB during commit (2 syncs shared between a group of transactions instead of 3). See for more information.

Redo Log Group Capacity

The redo log group capacity is the total combined size of all InnoDB redo logs. The relevant factors are:

• From , there is 1 redo log.

• The size of each redo log file is configured by the system variable. This can safely be set to a much higher value from . Before , resizing required the server to be restarted. From the variable can be set dynamically.

The redo log group capacity is determined by the following calculation:

innodb_log_group_capacity = *

For example, if is set to 2G and is set to 2, then we would have the following:

• innodb_log_group_capacity = *

• = 2G * 2

• = 4G

Changing the Redo Log Group Capacity

MariaDB starting with

The number of redo log files is fixed.

The size of redo log files can be changed with the following process:

• Stop the server.

• To change the log file size, configure .

• Start the server.

Log Sequence Number (LSN)

Records within the InnoDB redo log are identified via a log sequence number (LSN).

Checkpoints

When InnoDB performs a checkpoint, it writes the LSN of the oldest dirty page in the to the InnoDB redo log. If a page is the oldest dirty page in the , then that means that all pages with lower LSNs have been flushed to the physical InnoDB tablespace files. If the server were to crash, then InnoDB would perform crash recovery by only applying log records with LSNs that are greater than or equal to the LSN of the oldest dirty page written in the last checkpoint.

Checkpoints are one of the tasks performed by the InnoDB master background thread. This thread schedules checkpoints 7 seconds apart when the server is very active, but checkpoints can happen more frequently when the server is less active.

Dirty pages are not actually flushed from the buffer pool to the physical InnoDB tablespace files during a checkpoint. That process happens asynchronously on a continuous basis by InnoDB's write I/O background threads configured by the system variable. If you want to make this process more aggressive, then you can decrease the value of the system variable. You may also need to better tune InnoDB's I/O capacity on your system by setting the system variable.

Determining the Checkpoint Age

The checkpoint age is the amount of data written to the InnoDB redo log since the last checkpoint.

Determining the Checkpoint Age in InnoDB

MariaDB starting with

reintroduced the status variable for determining the checkpoint age.

The checkpoint age can also be determined by the process shown below.

To determine the InnoDB checkpoint age, do the following:

• Query .

• Find the LOG section:

• Perform the following calculation:

innodb_checkpoint_age = Log sequence number - Last checkpoint at

In the example above, that would be:

• innodb_checkpoint_age = Log sequence number - Last checkpoint at

• = 252794398789379 - 252792767756840

• = 1631032539 bytes

Determining the Redo Log Occupancy

The redo log occupancy is the percentage of the InnoDB redo log capacity that is taken up by dirty pages that have not yet been flushed to the physical InnoDB tablespace files in a checkpoint. Therefore, it's determined by the following calculation:

innodb_log_occupancy = /

For example, if is 1.5G and is 4G, then we would have the following:

• innodb_log_occupancy = /

• = 1.5G / 4G

• = 0.375

If the calculated value for redo log occupancy is too close to 1.0, then the InnoDB redo log capacity may be too small for the current workload.

Updates

A number of redo log improvements were made in :

• Autosize innodb_buffer_pool_chunk_size ().

• Improve the redo log for concurrency ().

• Remove FIL_PAGE_FILE_FLUSH_LSN ().

Before , created a zero-length ib_logfile0 as a dummy placeholder. From (), the size of that dummy file was increased to 12304 (0x3010) bytes, and all updates of FIL_PAGE_FILE_FLUSH_LSN in the first page of the system tablespace are removed.

From , if the server is started up with a zero-sized ib_logfile0, it is assumed that an upgrade is being performed after a backup had been prepared. The start LSN will then be read from FIL_PAGE_FILE_FLUSH_LSN, and a new log file are created starting from exactly that LSN.

Manually creating a zero-sized ib_logfile0 without manually updating the FIL_PAGE_FILE_FLUSH_LSN in the system tablespace to a recent enough LSN may result in error messages such as "page LSN is in the future". If a log was discarded while some changes had already been written to data pages, all sort of corruption may occur.

If the database was initialized with a server that never updates the FIL_PAGE_FILE_FLUSH_LSN field, then any server startup attempts with a zero-size ib_logfile0 are refused because of an invalid LSN. If that field was ever updated with a valid LSN by an older server, this safety mechanism cannot work, and the server may "rewind" to an earlier LSN.

_{This page is licensed: CC BY-SA / Gnu FDL}