近日,突然收到数据库同步延迟告警短信,登陆数据库后发现一个从节点确实存在复制延迟情况,并且还在不断增长
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发现数据库操作并不多。
[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
随后检查数据库中复制相关参数,发现了问题所在
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性能较差,就产生了延迟。
解决方案很简单,就是将这些信息都记录到表中,避免直接写盘
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繁忙度也下将了。记得将参数写入到配置文件中,避免重启失效。