innoDB 储存引擎 IO THread 后台线程 innodb_read_io_threads 和 innodb_write_io_threads 两个参数详解记录

在innodb储存中大量使用了AIO（Async IO）来处理些IO请求，这样可以极大提高数据库性能。而IO Thread 的主要工作是要负责这些IO请求的回调（call back）处理。innodb1.0版本之前共有4个IO Thread，分别是write，read，insert buffer，log IO thread。在Linux平台下，IO Thread的数量可以根据CPU核数来更改相应的参数值了，默认是4。，但是在Windows品台下可以通过 innodb_read_io_threads 和 innodb_write_io_threads 两个参数进行设置。

假如CPU是2颗8核的，那么可以设置：
innodb_read_io_threads = 8
innodb_write_io_threads = 8

如果数据库的读操作比写操作多，那么可以设置：
innodb_read_io_threads = 10
innodb_write_io_threads = 6

也就是说，你可以根据情况加以设置。

注意

这两个参数不支持动态改变，需要把该参数加入my.cnf里，修改完后重启MySQL服务，允许值的范围是1～64。

调整完成后，你可以用命令“show engine innodb status\G;”来查看调整结果，如下所示：
show engine innodb status
--------
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 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)