近日，突然收到数据库同步延迟告警短信，登陆数据库后发现一个从节点确实存在复制延迟情况，并且还在不断增长
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
|
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Queueing master event to the relay log
Master_Host: 10.0.139.161
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000228
Read_Master_Log_Pos: 930111496
Relay_Log_File: xxxx-relay-bin.000685
Relay_Log_Pos: 96105
Relay_Master_Log_File: mysql-bin.000228
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 930111496
Relay_Log_Space: 930112265
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 656
|
其中Slave_IO_State状态为Queueing master event to the relay log，该状态表示dump线程已经读取一个事件，正将它复制到relay log中，持续出现该状态说明复制性能较低。随后分析系统资源情况，CPU和内存使用率正常，IO却非常繁忙，但show processlist发现数据库操作并不多。
1
2
3
4
5
6
7
8
9
10
11
|
[root@Idmdb04-v-szzb ~]# iostat -d -x -k 1
Linux 2.6.32-696.el6.x86_64 (Idmdb04-v-szzb) 06/30/2020 _x86_64_ (8 CPU)
Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await r_await w_await svctm %util
sdb 0.00 19.10 1.59 21.96 175.02 207.76 32.52 0.01 0.49 2.58 0.34 0.31 0.74
sda 0.05 0.40 0.07 0.62 1.18 4.06 15.22 0.00 1.35 3.96 1.05 0.61 0.04
dm-0 0.00 0.00 0.07 0.97 0.99 3.88 9.32 0.00 1.33 3.18 1.19 0.33 0.03
dm-1 0.00 0.00 0.05 0.05 0.19 0.19 8.00 0.00 7.07 9.32 4.74 0.85 0.01
dm-2 0.00 0.00 7.89 49.60 953.34 322.45 44.39 0.04 0.76 2.05 0.55 0.27 98.00
sdc 0.00 2.41 6.30 6.20 778.32 114.72 142.94 0.02 1.27 1.92 0.62 0.67 0.84
sdd 0.00 0.00 0.00 0.00 0.00 0.00 8.00 0.00 0.01 0.01 0.00 0.01 0.00
|
随后检查数据库中复制相关参数，发现了问题所在
1
2
3
4
5
6
7
|
mysql> show variables where variable_name in('master_info_repository','relay_log_info_repository');
+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| master_info_repository | FILE |
| relay_log_info_repository | FILE |
+---------------------------+-------+
|
在复制原理中，IO-Thread会把从节点复制信息会把master传输过来的二进制日志内容并生成relay log，SQL-Thread负责操作回放relay log，执行状态信息保存在relay-log.info文件中。当前系统设置表示将这部分数据都写入到文件中，频繁的fsync必然导致性能下降，加上SATA盘IO性能较差，就产生了延迟。
解决方案很简单，就是将这些信息都记录到表中，避免直接写盘
1
2
3
4
5
|
mysql> stop slave;
mysql> set global master_info_repository='table';
mysql> set global relay_log_info_repository='table';
mysql> set global sync_master_info=1;
mysql> start slave;
|
修改完成后，延迟就消失了，并且磁盘IO繁忙度也下将了。记得将参数写入到配置文件中，避免重启失效。