Overview

The InnoDB Read I/O Threads handle completion of read I/O requests, and the InnoDB Write I/O Threads handle completion of write I/O requests.

Starting with MariaDB Enterprise Server 10.5 and MariaDB Community Server 10.5, the InnoDB I/O Threads were replaced by the asynchronous I/O functionality in the InnoDB Background Thread Pool.

For additional information, see "InnoDB I/O Threads".

This page describes how to configure the InnoDB I/O Threads.

Configure the Number of InnoDB 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.

Interaction with Asynchronous I/O

When asynchronous I/O is enabled, the InnoDB I/O Threads do not receive the initial I/O request from query threads. Instead, the query threads submit asynchronous I/O requests directly to the operating system, and after the operating system performs the operation, the InnoDB I/O Threads handle completion of the request.

Asynchronous I/O is enabled by the innodb_use_native_aio system variable, which is enabled by default.

Affected I/O Operations

The innodb_read_io_threads system variable affects completion of the following types of reads:

• Linear read-ahead (configured by innodb_read_ahead_threshold)
• Random read-ahead (configured by innodb_random_read_ahead)

The innodb_write_io_threads system variable affects completion of the following types of writes:

• Page flushing due to adaptive flushing (configured by innodb_adaptive_flushing and innodb_adaptive_flushing_lwm)
• Page flushing due to buffer pool capacity (configured by innodb_max_dirty_pages_pct] and [[innodb-system-variables/#innodb_max_dirty_pages_pct_lwm|innodb_max_dirty_pages_pct_lwm)
• Page flushing due to LRU page evictions (configured by innodb_lru_flush_size and innodb_lru_scan_depth)

Configuration Procedure

The method to configure the number of I/O threads depends on the server version and whether a server restart will be performed:

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 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 innodb_write_io_threadssystem 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:

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:

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.