Basic Details
The innodb_read_io_threads variable is for setting the dedicated number of threads for Innodb reads.
Releem automatically tunes innodb_read_io_threads and 44 other variables to improve MySQL performance. Try Releem for Free, or deepen your understanding by reading our detailed article.
Releem automatically tunes innodb_read_io_threads and 44 other variables to improve MySQL performance. Try Releem for Free, or deepen your understanding by reading our detailed article.
innodb_read_io_threads – Usage
Innodb_read_io_threads is the variable for configuring the number of InnoDB threads handling reads requests. The innodb_write_io_threads variable is the other half of the I/O threads equation and is for configuring the InnoDB threads handling write requests. These two variables work across MySQL, Percona, and MariaDB.
Each thread is capable of having 256 I/O requests in a pending state. For example, by setting innodb_read_io_threads to 2, the system will handle 512 concurrent asynchronous read I/O requests. InnoDB will attempt to balance the pending I/O requests across all available threads.
InnoDB I/O asynchronous subsystem is controlled by the innodb_use_native_aio variable, which is enabled by default.
Each thread is capable of having 256 I/O requests in a pending state. For example, by setting innodb_read_io_threads to 2, the system will handle 512 concurrent asynchronous read I/O requests. InnoDB will attempt to balance the pending I/O requests across all available threads.
InnoDB I/O asynchronous subsystem is controlled by the innodb_use_native_aio variable, which is enabled by default.
innodb_read_io_threads – Configuration
Innodb_read_io_threads variable cannot be configured dynamically in MySQL. You must set your intended value for innodb_read_io_threads using the configuration file (my.cnf or my.ini). Select a value for the number of threads between 1 and 64, with the default value being 4:
Configuration File:
[mysqld]
innodb_read_io_threads = XX
Replace XX with a value to suit your database needs. Must restart MySQL server to see changes applied. Refer to the considerations below if you regularly have nonzero values for queued I/O requests.
Configuration File:
[mysqld]
innodb_read_io_threads = XX
Replace XX with a value to suit your database needs. Must restart MySQL server to see changes applied. Refer to the considerations below if you regularly have nonzero values for queued I/O requests.
innodb_read_io_threads – Considerations
In most instances, database servers will not be overwhelmed by I/O requests. The default number of threads is usually adequate to handle the I/O request load. Because InnoDB uses Linux asynchronous I/O, each thread can handle multiple I/O requests at a time.
Generally, the only situation that warrants increasing the number of innodb_read_io_threads, is a high-end I/O system. Once the number of queued I/O requests exceeds 64, you may opt to increase the value of innodb_read_io_threads. Rarely, you may need to reduce the default value of 4 for innodb_read_io_threads, for Linux systems running multiple MariaDB servers to avoid exceeding system limits.
Generally, the only situation that warrants increasing the number of innodb_read_io_threads, is a high-end I/O system. Once the number of queued I/O requests exceeds 64, you may opt to increase the value of innodb_read_io_threads. Rarely, you may need to reduce the default value of 4 for innodb_read_io_threads, for Linux systems running multiple MariaDB servers to avoid exceeding system limits.
Releem will examine your I/O pending requests and I/O usage to determine if the value of innodb_read_io_threads needs to be adjusted and apply the configuration change automatically.