MariaDB Enterprise Server Configure the InnoDB I/O Threads

Configure the Number of InnoDB I/O Threads

In versions up to ES 10.4 and CS 10.4, the number of the InnoDB I/O Threads can be configured by setting the innodb_read_io_threads and innodb_write_io_threads system variables. If your server appears to be spending a lot of time waiting on I/O requests, then you may need to increase the number of I/O threads.

Starting with ES 10.5 and CS 10.5, the InnoDB I/O Threads have been replaced by the asynchronous I/O functionality in the InnoDB Background Thread Pool. In these versions, the innodb_read_io_threads and innodb_write_io_threads system variables have been repurposed. The value of each system variable is multiplied by 256 to determine the maximum number of concurrent asynchronous I/O requests that can be completed by the Background Thread Pool. For example, if innodb_read_io_threads=2 and innodb_write_io_threads=4 are set, InnoDB will be restricted to a maximum of 512 concurrent asynchronous read I/O requests and 1024 concurrent asynchronous write I/O requests.

Configure InnoDB's Maximum Number of Asynchronous I/O Requests with SET GLOBAL (ES 10.5) and Later

Starting with MariaDB Enterprise Server 10.5, InnoDB's maximum number of asynchronous I/O requests can be changed dynamically by setting the innodb_read_io_threads and innodb_write_io_threads system variables using the SET GLOBAL statement. The SET GLOBAL statement requires the SUPER privilege.

The value of each system variable is multiplied by 256 to determine the maximum number of asynchronous I/O requests that can be performed by the Background Thread Pool. For example, if you want to allow a maximum of 1024 concurrent asynchronous write I/O requests, the innodb_write_io_threads system variable should be set to 4 (since 1024/256=4).

To ensure that the change survives server restarts, the innodb_read_io_threads and innodb_write_io_threads system variables should also be set in a configuration file.

To configure InnoDB's maximum number of asynchronous I/O requests 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_read_io_threads and innodb_write_io_threads system variables to the new values using the SET GLOBAL statement.

   For example:

   SET GLOBAL innodb_read_io_threads=8;
   SET GLOBAL innodb_write_io_threads=8;
   

3. 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

4. Set the innodb_read_io_threads and innodb_write_io_threads system variables 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_read_io_threads=8
   innodb_write_io_threads=8
   

Configure the Number of InnoDB I/O Threads in a Configuration File

The number of I/O threads is configured by the innodb_read_io_threads and innodb_write_io_threads system variables.

To configure the number of InnoDB I/O Threads 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_read_io_threads and innodb_write_io_threads system variables 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_read_io_threads=8
   innodb_write_io_threads=8
   

3. Restart the server:

   $ sudo systemctl restart mariadb
   

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