InnoDB Redo Log
- Flushing Effects on Performance and Consistency
- Redo Log Group Capacity
- Log Sequence Number (LSN)
- Determining the Redo Log Occupancy
- MariaDB 10.8 Updates
Directly editing or moving the redo logs can cause corruption, and should never normally be attempted.
The redo log is used by InnoDB during crash recovery. The redo log files have names like
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:
- From MariaDB 10.5, there is 1 redo log. For MariaDB 10.4 and before, the number of redo log files is configured by the innodb_log_files_in_group system variable.
- The size of each redo log file is configured by the innodb_log_file_size system variable. This can safely be set to a much higher value from MariaDB 10.5. Before MariaDB 10.9, resizing required the server to be restarted. From MariaDB 10.9 the variable can be set dynamically.
The redo log group capacity is determined by the following calculation:
Changing the Redo Log Group Capacity
- Stop the server.
- To change the log file size, configure innodb_log_file_size. To increase the number of log files (until MariaDB 10.4 only), configure innodb_log_files_in_group.
- Start the server.
Log Sequence Number (LSN)
Records within the InnoDB redo log are identified via a log sequence number (LSN).
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
The checkpoint age can also be determined by the process shown below.
To determine the InnoDB checkpoint age, do the following:
- Query SHOW ENGINE INNODB STATUS.
- Find the
LOGsection. For example:
--- 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:
Log sequence number -
Last checkpoint at
In the example above, that would be:
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:
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:
- Autosize innodb_buffer_pool_chunk_size (MDEV-25342).
- Improve the redo log for concurrency (MDEV-14425).
- Remove FIL_PAGE_FILE_FLUSH_LSN (MDEV-27199).
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.