MariaDB Enterprise Server Configure the InnoDB Redo Log

Configure the InnoDB Redo Log Size

The size of the InnoDB Redo Log is configurable. If your server writes data at a very high frequency, then you may need to increase the redo log size, so that InnoDB does not have to perform checkpoints as frequently.

For the maximum capacity in the Redo Log, the Redo Log size should be the same as the InnoDB Buffer Pool size, which is configured by the innodb_buffer_pool_size system variable.

The method to configure the Redo Log size depends on the server version and whether a server restart will be performed:

Configure the InnoDB Redo Log Size with SET GLOBAL (ES 10.5 and Later)

Starting in MariaDB Enterprise Server 10.5, the size of the InnoDB Redo Log can be changed dynamically by setting the innodb_log_file_size system variable using the SET GLOBAL statement. The SET GLOBAL statement requires the SUPER privilege.

The resize operation is performed asynchronously in the background. If the server is restarted before the operation completes, the request may be ignored. To ensure that the change survives server restarts, the innodb_log_file_size system variable should also be set in a configuration file.

To configure the InnoDB Redo Log with the SET GLOBAL statement, use the following procedure:

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

   $ mariadb --user=root
   

2. Set the innodb_log_file_size system variable to the new size using the SET GLOBAL statement.

   For example, to set the size to 512 MB:

   SET GLOBAL innodb_log_file_size=(512 * 1024 * 1024);
   

   And to set the size to 2 GB:

   SET GLOBAL innodb_log_file_size=(2 * 1024 * 1024 * 1024);
   

3. The resize operation is performed asynchronously in the background. Confirm that the resize operation is complete by querying the innodb_log_file_size system variable using the SHOW GLOBAL VARIABLES statement. The resize operation is complete when the output shows the new size as the value of the system variable.

   Execute the following statement until it shows the new size:

   SHOW GLOBAL VARIABLES
      LIKE 'innodb_log_file_size';
   

4. 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 will be 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:

   Distributions	Example configuration file path
   CentOS RHEL Rocky Linux SLES	/etc/my.cnf.d/z-custom-mariadb.cnf
   Debian Ubuntu	/etc/mysql/mariadb.conf.d/z-custom-mariadb.cnf

5. Set the innodb_log_file_size system variable in the configuration file.

   It needs to be set in a group that will be 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, to set the size to 512 MB:

   [mariadb]
   ...
   innodb_log_file_size=512M
   

   And to set the size to 2 GB:

   [mariadb]
   ...
   innodb_log_file_size=2G
   

Configure the InnoDB Redo Log Size in a Configuration File (ES 10.5) and Later

Starting in MariaDB Enterprise Server 10.5, the size of the InnoDB Redo Log can be changed by setting the innodb_log_file_size system variable in a configuration file.

To configure the InnoDB Redo Log in a configuration file, use the following procedure:

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 will be 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:

   Distributions	Example configuration file path
   CentOS RHEL Rocky Linux SLES	/etc/my.cnf.d/z-custom-mariadb.cnf
   Debian Ubuntu	/etc/mysql/mariadb.conf.d/z-custom-mariadb.cnf

2. Set the innodb_log_file_size system variable in the configuration file.

   It needs to be set in a group that will be 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, to set the size to 512 MB:

   [mariadb]
   ...
   innodb_log_file_size=512M
   

   And to set the size to 2 GB:

   [mariadb]
   ...
   innodb_log_file_size=2G
   

3. Starting in MariaDB Community Server 10.5, the server must be restarted for the configuration change to take effect:

   $ sudo systemctl restart mariadb
   

   Starting in MariaDB Enterprise Server 10.5, the server can use the configuration change without a restart if you use SET GLOBAL.

Configure the InnoDB Redo Log Size in a Configuration File (ES 10.4 and Earlier)

In MariaDB Enterprise Server 10.4 and earlier, there can be multiple InnoDB Redo Log files.

The size of each Redo Log file is configured by the innodb_log_file_size system variable.

The number of Redo Log files is configured by the innodb_log_files_in_group system variable. Increasing the number of Redo Log files does not typically improve performance. In fact, using a single Redo Log file often performs best.

All Redo Log files are joined into a singular Redo Log, so the total Redo Log size is:

redo_log_size = innodb_log_file_size * innodb_log_files_in_group

To configure the InnoDB Redo Log size in a configuration file, use the following procedure:

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 will be 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:

   Distributions	Example configuration file path
   CentOS RHEL Rocky Linux SLES	/etc/my.cnf.d/z-custom-mariadb.cnf
   Debian Ubuntu	/etc/mysql/mariadb.conf.d/z-custom-mariadb.cnf

2. Set the innodb_log_file_size and innodb_log_files_in_group system variables in the configuration file.

   They need to be set in a group that will be read by MariaDB Server, such as [mariadb] or [server].

   When set in a configuration file, the innodb_log_file_size value supports units, such as "M", "G", etc.

   For example, to set the size of each log to 512 MB with only a single log file:

   [mariadb]
   ...
   innodb_log_file_size=512M
   innodb_log_files_in_group=1
   

   And to set the size of each log to 1 GB with 2 total log files:

   [mariadb]
   ...
   innodb_log_file_size=1G
   innodb_log_files_in_group=2
   

3. Restart the server:

   $ sudo systemctl restart mariadb