mysql innodb 参数_Mysql InnoDB性能参数--体系结构参数

Mysql InnoDB性能参数—体系结构参数https://www.cndba.cn/Marvinn/article/2761

自我笔记….

1、IO thread

https://www.cndba.cn/Marvinn/article/2761

MySQL5.5.X版本以上,innodb_read_io_threads和innodb_write_io_threads，取代了innodb_file_io_threads如此调整后，在Linux平台上就可以根据CPU核数来更改相应的参数值了，默认是4

https://www.cndba.cn/Marvinn/article/2761

mysql> show variables like 'innodb_version'/G

*************************** 1. row ***************************

Variable_name: innodb_version

Value: 5.7.20

1 row in set, 1 warning (0.00 sec)

mysql> show variables like 'innodb_%io_threads'/G

*************************** 1. row ***************************

Variable_name: innodb_read_io_threads

Value: 4

*************************** 2. row ***************************

Variable_name: innodb_write_io_threads

Value: 4

2 rows in set, 1 warning (0.00 sec)

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

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

innodb_read_io_threads = 8

innodb_write_io_threads = 8

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

innodb_read_io_threads = 10

innodb_write_io_threads = 6

调整完成后，你可以用命令“show engine innodb status/G;”来查看调整结果并且读线程ID总是小于写线程ID，如下所示：

mysql>show engine innodb status/G

--------

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)