【MySQL】MySQL从库延迟排查一例 - 《MySQL知识库》

一、登录数据库查看状态
二、原因分析

前言：周末春游的时候收到短信报警，从库延迟很高。手机VPN连进数据库以后排查情况，看到cpu使用率、磁盘IO、以及其他资源占用也不高。

一、登录数据库查看状态

MariaDB [trswcmtest]> show processlist;
+---------+-------------------+---------------------+------------+---------+------+-----------------------------------------------+------------------------------------------------------------------------------------------------------+----------+
| Id      | User              | Host                | db         | Command | Time | State                                         | Info                                                                                                 | Progress |
+---------+-------------------+---------------------+------------+---------+------+-----------------------------------------------+------------------------------------------------------------------------------------------------------+----------+
| 3769833 | system user       |                     | NULL       | Connect | 4484 | Waiting for master to send event              | NULL                                                                                                 |    0.000 |
| 3769885 | trs               | 10.200.74.146:63067 | trswcmtest | Sleep   | 4404 |                                               | NULL                                                                                                 |    0.000 |
| 3770353 | root              | localhost           | trswcmtest | Query   |    0 | init                                          | show processlist                                                                                     |    0.000 |
| 3770655 | system user       |                     | trswcmtest | Connect |    0 | updating                                      | update xxx set xxx=6925613 where xxx= 6925613                    |    0.000 |
| 3770656 | system user       |                     | trswcmtest | Connect |    0 | updating                                      | update xxxx set xxx=0,xxx=0,xxx=0,xxx='' where Me |    0.000 |
| 3770657 | trs               | %                   | trswcmtest | Connect |    0 | Sending data                                  | UPDATE xxxx SET xxx='<br>uwbdhlnqvybejm&nbsp;(d:\\temp\\收到附件，      |    0.000 |
| 3770658 | system user       |                     | trswcmtest | Connect |    0 | updating                                      | update xxxx set xxx=1032, xxx='system', xxx='2018-03-31 12:08:38' where xxx|    0.000 |
| 3770659 | system user       |                     | NULL       | Connect |    0 | Waiting for prior transaction to commit       | NULL                                                                                                 |    0.000 |
| 3770660 | system user       |                     | trswcmtest | Connect |    0 | updating                                      | UPDATE xxxx SET  xxx=1032,xxx='2018-03-31 12:08:38' where xxx=7150769             |    0.000 |
| 3770661 | system user       |                     | NULL       | Connect |    0 | Waiting for prior transaction to commit       | NULL                                                                                                 |    0.000 |
| 3770662 | system user       |                     | NULL       | Connect |    0 | Waiting for prior transaction to commit       | NULL                                                                                                 |    0.000 |
| 3770663 | system user       |                     | NULL       | Connect | 1583 | Waiting for room in worker thread event queue |

二、原因分析

从上面看到，进程3770663一直处于Waiting for room in worker thread event queue的状态,因为该从库开启了并行复制，查阅MariaDB官方文档了解到执行 Replication 时，会有两个 Thread，一个是 SQL_THREAD，另一个是 IO_THREAD，顾名思义，SQL_THREAD 就是处理SQL 作业的 Thread，IO_THREAD 就是管理Binlog 从 Master 拿来然后回放在自己的 Relay Log 的 Thread。

在没有启用并行复制时，sql线程就自己分配给自己，也因为没法先看一下Binlog后面的事务，也许后面可以先做的，但是还是得一个一个回放。
当有并行复制时，sql线程除了自己变成分配工作的线程以外，会产生出slave_parallel_threads数量的线程来处理事务，在分配工作时就知道哪些事务没有顺序关系，哪些有一定的顺序，这样回放就会快得多。
但是怎么会出现 Waiting for room in worker thread event queue.呢？
从官方文档 slave-parallel-max-queued

文章看一半就不会注意到这个参数，如果你的事务在每个slave-parallel-max-queued大小的Transation Logs都能被解析，这样就有助于平行处理，但是如果无法解析，那就执行等到预读先做完，有空间了再读后面的Log并且继续执行，这样可能平行处理的顺序会被打乱。

所以适当地设定 slave-parallel-max-queued是必须的，但是它跟 slave_parallel_threads的使用是有关系的
slave_parallel_threads * slave_parallel_max_queued 为占用的总内存，如果太大会造成OOM，我这里设置的128M，后续再进行观察。