The redo log is used by InnoDB/XtraDB during crash recovery. The redo log files have names like ib_logfileN, where 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.
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.
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.
The redo log group capacity is the total combined size of all InnoDB redo logs. The relevant factors are:
Therefore, 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:
innodb_log_group_capacity = innodb_log_file_size * innodb_log_files_in_group 2G * 2 4G In MariaDB 10.0 and later, the number or size of redo log files can be changed with the following process:
In MariaDB 5.5 and before, the number or size of redo log files can be changed with the following process:
2, then set it to 1: SET GLOBAL innodb_fast_shutdown = 1;
datadir. 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.
The checkpoint age is the amount of data written to the InnoDB redo log since the last checkpoint.
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:
LOG section. 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
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 In MariaDB 10.1 and before, MariaDB uses XtraDB by default. In those versions, the checkpoint age can be determined by the Innodb_checkpoint_age status variable.
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:
innodb_log_occupancy = innodb_checkpoint_age / innodb_log_group_capacity 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.
© 2019 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.
https://mariadb.com/kb/en/innodb-redo-log/