Configuring the InnoDB Purge Threads

The InnoDB purge threads delete previous versions of rows that have been deleted. The purge threads continuously perform this work in the background.

The number of the InnoDB purge threads is configurable. If your server deletes or updates rows at a very high frequency, then you may need to increase the number of purge threads.

Dynamically Changing the Number of InnoDB Purge Threads

In MariaDB Enterprise Server 10.5, the number of InnoDB purge threads 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_purge_threads system variable to the new size using the SET GLOBAL statement.

    For example:

    SET GLOBAL innodb_purge_threads=8;
    
  3. Choose a configuration file in which to configure the innodb_purge_threads 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_purge_threads 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].

    For example:

    [mariadb]
    ...
    innodb_purge_threads=8
    

Configuring the the Number of InnoDB Purge Threads

The number of purge threads is configured by the innodb_purge_threads system variable.

  1. Choose a configuration file in which to configure the innodb_purge_threads 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_purge_threads 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].

    For example:

    [mariadb]
    ...
    innodb_purge_threads=8
    
  3. Restart the server. You can avoid the need to restart in MariaDB Enterprise Server 10.5 by dynamically changing the number of purge threads.

    To restart the server on operating systems that use systemd:

    $ sudo systemctl restart mariadb
    

    On systems that use Upstart:

    $ sudo service mysql restart