MySql教程

MySQL多线程复制统计信息

本文主要是介绍MySQL多线程复制统计信息,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

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

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

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的值,可以从错误日志文件中获取有关多线程性能如何的信息。

 

这篇关于MySQL多线程复制统计信息的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!