Skip navigation links
13.5.4 Maria Recovery »
Section Navigation      [Toggle]

13.5.3. Maria Transaction Log

The transaction log within Maria keeps a record of all changes, including DDL, to tables created using the TRANSACTIONAL table option. Events from all tables and all databases are written into a single log file sequence. The Maria log consists of one log control file (maria_log_control) and one or more maria log files (named maria_log.????????, where ???????? is an eight-digit number with a maximum value of 16777215). The log control file and log file are created automatically when mysqld is started.

The log contains a copy of all the data and the log can be used to replay and verify the contents of the data files in the event of a crash. Checkpoints saves information about the current transactions, opened files and other statusw information that will be required if the log needs to be used during recover. Checkpoints are written every 30 seconds by default, although you can increase or decrease this value.

For tables using the transactional format, statements that change data (DML statements) are recorded in the log file and these changes are also ultimately written to the data and index files. There is no fixed point when the application of data written to the log is also written to the data and index files. The process happens continuously in the background during normal execution. Although the data and index files and the transaction log may be out of sync, all of the information is always available. In the event of a crash, the recovery process will replay and apply the contents of the log to bring the data and log files back into synchronization.

Additional log files are created when the log file reaches the configured maximum size (as controlled by maria_log_file_size). The default value is 1GB. This option can be controlled as a global variable and set with the configuration file or on the command line.

You can determine the list of current log files using the statement SHOW ENGINE MARIA LOGS: 

mysql> SHOW ENGINE MARIA LOGS;
+--------+-------------------------------------------------------------+----------+
| Type   | Name                                                        | Status   |
+--------+-------------------------------------------------------------+----------+
| maria  | Size    191627264 ; /usr/local/mysql/var/maria_log.00000009 | unknown  |
+--------+-------------------------------------------------------------+----------+
1 row in set (0.00 sec)

The Status shows the current status of the log file:

  • unknown — no checkpoint has taken place, so the current status of the log file is unknown.

  • in-use — information is still being written to or read from the log, or outstanding statements are still active that have information active within the log file.

  • free — the log file and any statements related to it have been completed and safely committed to disk, and the log file is no longer active.

Any other message indicates an error in reading the log file information.

New log files are created automatically as the size of the current log file reaches the configured size. For example, shown below are the log files after a statement that inserted a large volume of data was executed after the maria_log_file_size variable has been reduced to 8MB (the minimum supported value): 

mysql> SHOW ENGINE MARIA LOGS;
+--------+-------------------------------------------------------------+---------+
| Type   | Name                                                        | Status  |
+--------+-------------------------------------------------------------+---------+
| maria  | Size    191627264 ; /usr/local/mysql/var/maria_log.00000009 | in use  |
| maria  | Size      8388608 ; /usr/local/mysql/var/maria_log.00000010 | in use  |
| maria  | Size      8388608 ; /usr/local/mysql/var/maria_log.00000011 | in use  |
| maria  | Size      8388608 ; /usr/local/mysql/var/maria_log.00000012 | in use  |
| maria  | Size      8388608 ; /usr/local/mysql/var/maria_log.00000013 | in use  |
| maria  | Size      8388608 ; /usr/local/mysql/var/maria_log.00000014 | in use  |
| maria  | Size      8388608 ; /usr/local/mysql/var/maria_log.00000015 | in use  |
| maria  | Size       139264 ; /usr/local/mysql/var/maria_log.00000016 | in use  |
+--------+-------------------------------------------------------------+---------+
8 rows in set (0.00 sec)

Log files that no longer have transactions or outstanding events where the data has been safely committed on disk are marked 'free': 

mysql> SHOW ENGINE MARIA LOGS;
+--------+-------------------------------------------------------------+---------+
| Type   | Name                                                        | Status  |
+--------+-------------------------------------------------------------+---------+
| maria  | Size      8388608 ; /usr/local/mysql/var/maria_log.00000002 | free    |
| maria  | Size      2170880 ; /usr/local/mysql/var/maria_log.00000003 | in use  |
+--------+-------------------------------------------------------------+---------+
2 rows in set (0.00 sec)

Log files are deleted according to the setting of the maria_log_purge_type dynamic variable. Three options are supported, immediate, external and at_flush.

In immediate mode, the log files are deleted as soon as they no longer have outstanding transactions or events. This reduces the disk space used by these logs, but means that the logs cannot be transferred to another machine for replaying. This setting should not affect recovery functionality, as only logs where there is no outstanding data to be committed are deleted.

In at_flush mode, the log files are only deleted when you execute the FLUSH LOGS statement. Issuing this statement will delete the logs that have the 'free' status, and therefore will not affect logs with outstanding transactions or events. 

mysql> SHOW ENGINE MARIA LOGS;
+--------+-------------------------------------------------------------+---------+
| Type   | Name                                                        | Status  |
+--------+-------------------------------------------------------------+---------+
| maria  | Size      8388608 ; /usr/local/mysql/var/maria_log.00000002 | free    |
| maria  | Size      8388608 ; /usr/local/mysql/var/maria_log.00000003 | free    |
| maria  | Size      8388608 ; /usr/local/mysql/var/maria_log.00000004 | free    |
| maria  | Size      4325376 ; /usr/local/mysql/var/maria_log.00000005 | in use  |
+--------+-------------------------------------------------------------+---------+
4 rows in set (0.00 sec)

mysql> FLUSH LOGS;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW ENGINE MARIA LOGS;
+--------+-------------------------------------------------------------+---------+
| Type   | Name                                                        | Status  |
+--------+-------------------------------------------------------------+---------+
| maria  | Size      4325376 ; /usr/local/mysql/var/maria_log.00000005 | in use  |
+--------+-------------------------------------------------------------+---------+
1 row in set (0.00 sec)

When maria_log_purge_type is set to external, log files are not deleted by MySQL at all. Instead, it is assumed that an external process is responsible for deleting log files. Care should be taken with this setting as you should only delete log files that Maria is no longer using.

Transaction log file events can be viewed using the maria_read_log command, which also provides the ability to replay log file contents and apply them to tables without running mysqld. For more information, see Section 13.5.8, “Maria Command-line Tools”.