InnoDB Redo Log
- Flushing Effects on Performance and Consistency
- Redo Log Group Capacity
- Log Sequence Number (LSN)
- Determining the Checkpoint Age
- Determining the Redo Log Occupancy
The redo log is used by InnoDB/XtraDB during crash recovery. The redo log files have names like
N is an integer. 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.
Therefore, redo log group capacity is determined by the following calculation:
Changing the Redo Log Group Capacity
MariaDB starting with 10.0
MariaDB until 5.5
In MariaDB 5.5 and before, the number or size of redo log files can be changed with the following process:
- If innodb_fast_shutdown is set to
2, then set it to
SET GLOBAL innodb_fast_shutdown = 1;
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.2
In MariaDB 10.2 and later, MariaDB uses InnoDB. In those versions, the checkpoint age can 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 Checkpoint Age in XtraDB
MariaDB until 10.1
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.