Configure the InnoDB Undo Log

Overview

The InnoDB undo log is a transaction log used by InnoDB to keep track of multiple row versions for multi-version concurrency control (MVCC). When a row's value changes, InnoDB stores old versions of the row in the Undo Log.

When transactions are committed and the old row versions are no longer necessary, the InnoDB Purge Threads asynchronously delete old row versions from the Undo Log in the background.

When a transaction is rolled back, InnoDB uses the Undo Log to rollback the transaction's changes.

For additional information, see "InnoDB Undo Log".

This page describes how to configure the InnoDB Undo Log.

Configure InnoDB Undo Log Tablespaces

By default, the InnoDB undo log is located in the InnoDB system tablespace, which is defined by the system variable. However, it can be helpful to configure separate undo log tablespaces to spread out I/O load between different files or storage devices.

InnoDB can be configured to use separate undo log tablespaces by setting the system variable. The separate undo log tablespaces will have file names of the format undoN, where N is an integer.

When you configure separate undo log tablespaces, you can also configure the separate undo log tablespaces to go to a specific directory by setting the system variable. This is most helpful if you want to put the undo log tablespaces on a separate storage device.

Separate InnoDB undo log tablespaces must be configured prior to the initialization of the server's InnoDB data directory. If you try to configure separate InnoDB undo log tablespaces when the InnoDB data directory has already been initializes, you will see errors in the error log during startup similar to the following:

To safely configure separate InnoDB undo log tablespaces:

1. If you have preexisting data, backup your data with .

2. Ensure that the server is stopped:

1. Choose a configuration file for custom changes to system variables and options.

It is not recommended to make custom changes to Enterprise Server's default configuration files, because your custom changes can be overwritten by other default configuration files that are loaded after.

Ensure that your custom changes are read last by creating a custom configuration file in one of the included directories. Configuration files in included directories are read in alphabetical order. Ensure that your custom configuration file is read last by using the z- prefix in the file name.

Some example configuration file paths for different distributions are shown in the following table:

1. Set the system variable in the configuration file.

It needs to be set in a group that are read by MariaDB Server, such as [mariadb] or [server].

For example, to set the number of tablespaces to 8:

1. If you want your InnoDB undo log tablespaces to be in a specific directory, then also set the system variable in the configuration file:

For example, to set the directory to /innodb/undo:

1. If you want your InnoDB undo log tablespaces to be in a specific directory, then also create the directory, and give it the proper permissions:

1. Delete the current contents of the datadir and innodb_data_home_dir.

For example, if the default value of /var/lib/mysql is used for both:

1. Reinitialize the data directory using the MariaDB Install DB command.

2. Start the server:

1. Connect to the server using MariaDB Client:

1. If your server had preexisting data, then reload the backup taken at the beginning of the procedure.

2. Confirm that the configuration changes were properly applied by checking the values of the system variables using the statement:

1. Consider also enabling undo log truncation to increase performance of the InnoDB Purge Threads.

Enable InnoDB Undo Log Truncation

If a server is configured to have 2 or more separate InnoDB undo log files, then InnoDB undo log truncation can be enabled by setting the system variable using the statement. The statement requires the SUPER privilege.

When InnoDB undo log truncation is enabled, the InnoDB purge threads can truncate an entire undo log at once, rather than individually freeing each rollback segment within the undo log.

An undo log is truncated when its size exceeds the system variable.

The frequency at which the InnoDB purge threads check for undo logs to truncate is configured by setting the innodb_purge_rseg_truncate_frequency system variable using the statement.

To ensure that the changes survive server restarts, the system variables should also be set in a configuration file.

To enable InnoDB undo log truncation:

1. Connect to the server using MariaDB Client as the root@localhost user account or another user account with the SUPER privilege:

1. Set the system variable to ON using the statement.

For example:

1. If you would like to change the size at which undo logs are truncated, then also set the system variable to the new size using the statement.

For example, to set the size to 2 GB:

1. If you would like the InnoDB purge threads to check the undo logs more frequently, then also set the system variable to a lower value using the statement.

For example, to configure the purge threads to check the undo logs for truncation every 64 iterations:

1. Choose a configuration file for custom changes to system variables and options. It is not recommended to make custom changes to Enterprise Server's default configuration files, because your custom changes can be overwritten by other default configuration files that are loaded after.

Ensure that your custom changes are read last by creating a custom configuration file in one of the included directories. Configuration files in included directories are read in alphabetical order. Ensure that your custom configuration file is read last by using the z- prefix in the file name.

Some example configuration file paths for different distributions are shown in the following table:

1. Set the system variables in the configuration file. It needs to be set in a group that are read by MariaDB Server, such as [mariadb] or [server]. When set in a configuration file, the value supports units, such as "M", "G", etc.

For example:

_{This page is: Copyright © 2025 MariaDB. All rights reserved.}