MySQL多线程复制统计信息

MySQL从5.6开始支持多线程复制，即MTS(Multi-threaded Slave)。
开启MTS需要将参数slave_parallel_workers设置成大于1；

开启MTS之后，会将一些多线程复制的统计信息写入error日志中。例如：

?

1 2

2021-12-23T04:19:28.704457Z 6137458 [Note] Multi-threaded slave statistics for channel '': seconds elapsed = 733; events assigned = 37952513; worker queues filled over overrun level = 0; waited due a Worker queue full = 0; waited due the total size = 0; waited at clock conflicts = 357338533800 waited (count) when Workers occupied = 19795 waited when Workers occupied = 101620098100 2021-12-23T05:29:48.957691Z 6137458 [Note] Multi-threaded slave statistics for channel '': seconds elapsed = 4220; events assigned = 37957633; worker queues filled over overrun level = 0; waited due a Worker queue full = 0; waited due the total size = 0; waited at clock conflicts = 357338533800 waited (count) when Workers occupied = 19795 waited when Workers occupied = 101620098100

　　

seconds elapsed = 810;
#上一次统计和这一次统计的时间间隔

events assigned = 37951489;
#总共有多少个events被分配执行，这里统计的是总数

worker queues filled over overrun level = 0;
#MTS倾向于在并行workers之间进行负载均衡，参数slave_parallel_workers参数指定了有多少个并行复制workers线程，该项统计信息显示workers的饱和程度。如果并行的worker队列都接近满了，该计数器就会增加，且为了避免达到worker队列的限制，复制事件就会延迟。

waited due a Worker queue full = 0;
#因为workers队列爆满，协调线程必须等待，该统计参数会增长

waited due the total size = 0;
#该参数代表worker队列持有未应用事件，达到了可用内存的限制，造成协调线程睡眠的次数。如果这个值持续增长，需要增大slave_pending_jobs_size_max值来避免协调线程等待时间。

waited at clock conflicts = 357338533800
#事务之间存在依赖的情况下，该参数显示等待时间，相当于冲突检测和解决方案的逻辑时间。

waited (count) when Workers occupied = 19795
#协调进程监控worker足额（enough）分配的统计次数。enough定义取决于调度类型（基于每个库和时钟）

waited when Workers occupied = 101620098100
#协调线程等待worker线程可用的时间，并且仅适用于提交时钟调度程序(Commit-clock scheduler)。

 

waited at clock conflicts、waited (count) when Workers occupied、waited when Workers occupied都是累计值。

通过将log-warnings变量更改为大于1的值，可以从错误日志文件中获取有关多线程性能如何的信息。