Configuring the InnoDB Redo Log

The InnoDB redo log is used to store changes to data in a crash-safe manner. If the server crashes, InnoDB uses the redo log during the next server restart to perform crash recovery. During crash recovery, InnoDB searches for redo log records since the last checkpoint, and it applies the changes from those redo log records to the actual tablespace files.

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.

Dynamically Resizing the InnoDB Redo Log

In MariaDB Enterprise Server 10.5, the size of the InnoDB redo log can be changed dynamically. To ensure that the change survives server restarts, the change should also be made in a configuration file.

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

    $ sudo mariadb
    
  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. Choose a configuration file in which to configure the innodb_log_file_size system variable.

    It not recommended to make custom changes to one of the bundled configuration files. Instead, it is recommended to create a custom configuration file in one of the included directories. Configuration files in included directories are read in alphabetical order. If you want your custom configuration file to override the bundled configuration files, then it is a good idea to prefix the custom configuration file's name with a string that will be sorted last, such as z-.

    • On RHEL, CentOS, and SLES, a good custom configuration file would be: /etc/my.cnf.d/z-custom-my.cnf

    • On Debian and Ubuntu, a good custom configuration file would be: /etc/mysql/mariadb.conf.d/z-custom-my.cnf

  4. 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 "MB", "GB", etc.

    For example, to set the size to 512 MB:

    [mariadb]
    ...
    innodb_log_file_size=512MB
    

    And to set the size to 2 GB:

    [mariadb]
    ...
    innodb_log_file_size=2GB
    

Configuring the InnoDB Redo Log Size

The InnoDB redo log size is configured differently in different versions of MariaDB Server.

Configuring the InnoDB Redo Log Size in MariaDB Server 10.5

In MariaDB Enterprise Server 10.5 and MariaDB Community Server 10.5, there is only a single InnoDB redo log, and its size is configured by the innodb_log_file_size system variable.

  1. Choose a configuration file in which to configure the innodb_log_file_size system variable.

    It not recommended to make custom changes to one of the bundled configuration files. Instead, it is recommended to create a custom configuration file in one of the included directories. Configuration files in included directories are read in alphabetical order. If you want your custom configuration file to override the bundled configuration files, then it is a good idea to prefix the custom configuration file's name with a string that will be sorted last, such as z-.

    • On RHEL, CentOS, and SLES, a good custom configuration file would be: /etc/my.cnf.d/z-custom-my.cnf

    • On Debian and Ubuntu, a good custom configuration file would be: /etc/mysql/mariadb.conf.d/z-custom-my.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 "MB", "GB", etc.

    For example, to set the size to 512 MB:

    [mariadb]
    ...
    innodb_log_file_size=512MB
    

    And to set the size to 2 GB:

    [mariadb]
    ...
    innodb_log_file_size=2GB
    
  3. In MariaDB Community Server 10.5, you must restart the server. You can avoid the need to restart in MariaDB Enterprise Server 10.5 by dynamically resizing the redo log.

    To restart the server on operating systems that use systemd:

    $ sudo systemctl restart mariadb
    

    On systems that use Upstart:

    $ sudo service mysql restart
    

Configuring the InnoDB Redo Log Size in MariaDB Server 10.4 and Earlier

In MariaDB Enterprise Server 10.4, MariaDB Community Server 10.4, and even older versions, there can be multiple InnoDB redo logs. The size of each redo log is configured by the innodb_log_file_size system variable. The number of redo logs is configured by the innodb_log_files_in_group system variable.

  1. Choose a configuration file in which to configure the innodb_log_file_size and innodb_log_files_in_group system variables.

    It not recommended to make custom changes to one of the bundled configuration files. Instead, it is recommended to create a custom configuration file in one of the included directories. Configuration files in included directories are read in alphabetical order. If you want your custom configuration file to override the bundled configuration files, then it is a good idea to prefix the custom configuration file's name with a string that will be sorted last, such as z-.

    • On RHEL, CentOS, and SLES, a good custom configuration file would be: /etc/my.cnf.d/z-custom-my.cnf

    • On Debian and Ubuntu, a good custom configuration file would be: /etc/mysql/mariadb.conf.d/z-custom-my.cnf

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

    It 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 value supports units, such as "MB", "GB", etc.

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

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

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

    [mariadb]
    ...
    innodb_log_file_size=1GB
    innodb_log_files_in_group=2
    
  3. Restart the server.

    On operating systems that use systemd:

    $ sudo systemctl restart mariadb
    

    On systems that use Upstart:

    $ sudo service mysql restart