Basic Details
The innodb_write_io_threads variable is for setting the dedicated number of threads for Innodb write operations.
Releem automatically tunes innodb_write_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_write_io_threads and 44 other variables to improve MySQL performance. Try Releem for Free, or deepen your understanding by reading our detailed article.
innodb_write_io_threads – Usage
Innodb_write_io_threads is a variable closely related to innodb_read_io_threads. Set the number of InnoDB threads handling write operations, using innodb_write_io_threads. The innodb_read_io_threads sets the number of InnoDB threads handling read requests. These variables work with MySQL, Percona, and MariaDB.
256 I/O requests can be in a pending state for each I/O write thread. Setting innodb_write_io_threads to 4, means the system will handle 1024 concurrent asynchronous write 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.
256 I/O requests can be in a pending state for each I/O write thread. Setting innodb_write_io_threads to 4, means the system will handle 1024 concurrent asynchronous write 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_write_io_threads – Configuration
Innodb_write_io_threads variable cannot be configured dynamically. You must set your intended value for innodb_write_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_write_io_threads = XX
Replace XX with a value to suit your database needs. Must restart MySQL servers to see changes applied.
Configuration File:
[mysqld]
innodb_write_io_threads = XX
Replace XX with a value to suit your database needs. Must restart MySQL servers to see changes applied.
innodb_write_io_threads – Considerations
Typically the default number of innodb_write_io_threads, 4, is highly capable of handling the I/O request load without performance degradation. InnoDB uses Linux asynchronous I/O, so each thread can handle multiple I/O requests at a time.
Most situations that require adjusting innodb_write_io_threads involve high-end I/O systems with write-intensive loads. Once the number of queued I/O requests exceeds 64, you may opt to increase the value of innodb_write_io_threads. For Linux systems running multiple MariaDB servers, you may rarely need to reduce the default value of innodb_write_io_threads, to avoid exceeding system limits.
Most situations that require adjusting innodb_write_io_threads involve high-end I/O systems with write-intensive loads. Once the number of queued I/O requests exceeds 64, you may opt to increase the value of innodb_write_io_threads. For Linux systems running multiple MariaDB servers, you may rarely need to reduce the default value of innodb_write_io_threads, to avoid exceeding system limits.
Let Releem handle these configuration considerations for you. Releem will examine your I/O pending requests and I/O usage to determine if the value of innodb_write_io_threads needs to be adjusted and apply the configuration change automatically.