How do you tune innodb_read_io_threads?

Question

The default value of innodb_read_io_threads and innodb_write_io_threads is 4. How do you check if your server load needs a higher number of threads?

As I regularly check show engine innodb status \G, there is no traceable activity:

--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
...
I/O thread 32 state: waiting for completed aio requests (write thread)
I/O thread 33 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0] , aio writes: [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0] ,

Furthermore, what is the downside of increasing it to the maximum value of 64? If not using, they are harmless idle threads.

Answer 1

Pending normal aio reads: [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0] , aio writes: [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0] ,

These numbers correspond to your read threads and write threads. You can see they are all zero. That means all I/O has been taken care of, i.e. there are no queued I/O requests waiting for a free thread. So no need to increase the number of threads.

If you regularly see nonzero values in this line, it could mean you have heavy I/O activity, to the degree that your existing threads can't keep up with the demand.

In my experience, this is very unusual for any database server to be so overwhelmed with I/O requests. In almost all cases, the default number of threads is easily enough to handle the load. Besides, each thread can handle multiple I/O requests concurrently, because it uses asynchronous I/O.

There's not much downside to increasing the number of threads higher even if your database server doesn't need it. It just means there will be a longer line of 0's. But it's not needed.

As a general rule, I'd prefer to keep as few changes to the MySQL configuration file as are necessary. Use defaults for everything, unless there's a real reason to change it.

If you set these config options higher, then eventually, someone new to your team will ask, "why are the innodb_*_io_threads value set so high?" and you'll have to explain all over again why you thought it was so important to raise a configuration value that didn't need to be raised.