The Ultimate Guide to High Availability with MariaDB

InnoDB Redo Log

Contents

  1. Overview
  2. Flushing Effects on Performance and Consistency
    1. Binary Log Group Commit and Redo Log Flushing
  3. Redo Log Group Capacity
    1. Changing the Redo Log Group Capacity
  4. Log Sequence Number (LSN)
  5. Checkpoints
    1. Determining the Checkpoint Age
      1. Determining the Checkpoint Age in InnoDB
  6. Determining the Redo Log Occupancy
  7. MariaDB 10.8 Updates

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

Overview

The redo log is used by InnoDB during crash recovery and background flushing of transactions to the tablespaces. The redo log files have names like ib_logfileN, where N is an integer. From MariaDB 10.5, there is only one redo log, so the file will always be named ib_logfile0. If the innodb_log_group_home_dir system variable is configured, then the redo log files will be created in that directory. Otherwise, they will be created in the directory defined by the datadir system variable.

Flushing Effects on Performance and Consistency

The innodb_flush_log_at_trx_commit 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

In MariaDB 10.0 and above, when both innodb_flush_log_at_trx_commit=1 (the default) is set and the binary log is enabled, there is now one less sync to disk inside InnoDB during commit (2 syncs shared between a group of transactions instead of 3). See Binary Log Group Commit and InnoDB Flushing Performance 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:

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

innodb_log_group_capacity = innodb_log_file_size * innodb_log_files_in_group

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

Changing the Redo Log Group Capacity

The number (until MariaDB 10.4 only - from MariaDB 10.5 there is only 1 redo log) or size of redo log files can be changed with the following process:

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 InnoDB buffer pool to the InnoDB redo log. If a page is the oldest dirty page in the InnoDB buffer pool, 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 innodb_write_io_threads system variable. If you want to make this process more aggressive, then you can decrease the value of the innodb_max_dirty_pages_pct system variable. You may also need to better tune InnoDB's I/O capacity on your system by setting the innodb_io_capacity 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 10.5

MariaDB 10.5 reintroduced the Innodb_checkpoint_age status variable (available in XtraDB until MariaDB 10.1) 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:

---
LOG
---
Log sequence number 252794398789379
Log flushed up to 252794398789379
Pages flushed up to 252792767756840
Last checkpoint at 252792767756840
0 pending log flushes, 0 pending chkp writes
23930412 log i/o's done, 2.03 log i/o's/second
  • 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
  • = 1631032539 byes / (1024 * 1024 * 1024) (GB/bytes)
  • = 1.5 GB of redo log written since last checkpoint

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 = innodb_checkpoint_age / innodb_log_group_capacity

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

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.

MariaDB 10.8 Updates

A number of redo log improvements were made in MariaDB 10.8:

Before MariaDB 10.8.1, mariadb-backup --prepare created a zero-length ib_logfile0 as a dummy placeholder. From MariaDB 10.8.1 (MDEV-14425), 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 MariaDB 10.8.1, 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 will be 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 will be 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.

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.