8

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.

1 Answer 1

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

5
  • 8
    Pro tip: Write comments in your MySQL configuration file, to explain exactly why you chose the value for each option, if you set them to a value other than the default. Anyone who has to maintain your database server will thank you (including you in six months when you've forgotten why you set each option). Sep 12, 2021 at 16:10
  • For some reason, my show engine status does not have this string? What does this means? There other answers that suggests if you have more threads if you have more tables/ibd files?
    – Freedo
    Dec 4, 2021 at 6:41
  • @Freedo Did you read my answer above? Dec 4, 2021 at 6:44
  • My logs doesn't show Pending normal aio reads: at all
    – Freedo
    Dec 4, 2021 at 6:47
  • What does SELECT VERSION(); return? Dec 4, 2021 at 15:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.