MySQL Config--PXC限流和innodb_io_capacity参数设置
问题描述
某PXC集群节点在凌晨2:06到2:13之间发出流控：
排查WSRep接收队列和发送队列，发现是WSRep接收队列已经打满：
根据WSRep接收队列打满情况，发现WSRep接收数据上涨：
排查该节点所在服务器的相关核心指标，发现CPU/内存/磁盘/网络等使用率都很低，服务器CPU使用率低于10%：
服务器IO使用率低于30%：
排查中发现PXC集群实例的buffer_page_writter指标存在异常，长期稳定在2000左右：
排查发现，未显式配置参数innodb_io_capacity和innodb_io_capacity_max，其默认值为：
mysql> show variables like '%innodb_io_capacity%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| innodb_io_capacity | 200 |
| innodb_io_capacity_max | 2000 |
+------------------------+-------+
2 rows in set (0.00 sec)
相关参数介绍：
innodb_io_capacity:
The innodb_io_capacity variable defines the number of I/O operations per second (IOPS) available to InnoDB background tasks, such as flushing pages from the buffer pool and merging data from the change buffer.
innodb_io_capacity_max:
If flushing activity falls behind, InnoDB can flush more aggressively, at a higher rate of I/O operations per second (IOPS) than defined by the innodb_io_capacity variable. The innodb_io_capacity_max variable defines a maximum number of IOPS performed by InnoDB background tasks in such situations.
参数innodb_io_capacity和innodb_io_capacity_max主要影响MySQL将脏页从Buffer Pool刷回磁盘的写操作速度，并不影响从磁盘读取数据页到Buffer Pool的读操作速度，因此可以发现PXC集群实例的buffer_page_read指标远高于参数innodb_io_capacity_max值：
根据上面的分析结果，我们快速调整这两个参数值为：
mysql> show variables like '%innodb_io_capacity%';
+------------------------+--------+
| Variable_name | Value |
+------------------------+--------+
| innodb_io_capacity | 2000 |
| innodb_io_capacity_max | 20000 |
+------------------------+--------+
2 rows in set (0.00 sec)
同时考虑到PXC主节点的写入较大，将参数wsrep_slave_threads的值从24调整到64，集群实例的buffer_page_writter指标值从2000快速飙升至5963，处理完"积压数据"后开始回落，集群实例的WSRep接收队列也从1048快速恢复到0，PXC集群流控恢复正常。
