MySQL 5.7--复制延迟监控

SHOW PROCESSLIST方式

为保证二进制日志在从库的执行时间和顺序的正确性，二进制日志中的每个语句都设置了时间戳，因此如果主库上的语句正不断执行，那么可以根据最后一条执行语句时间戳和当前备库时间进行对比，可以估算出出复制延迟的时间。

使用SHOW PROCESSLIST可以看出从库上最后一次执行语句的时间戳距离当前时间：

如果主库上正不断执行SQL，那么SHOW PROCESSLIST看到的TIME可以当作复制的延迟。

如果主库已经一段时间没有执行SQL，那么SHOW PROCESSLIST看到的TIME只能当作从库上最近一次执行SQL时间点到当前时间点的间隔。

 

SHOW SLAVE STATUS方式

执行SHOW SLAVE STATUS查看复制信息，使用Seconds_Behind_Master来查看从库落后主库的秒数。

复制延迟时间=从库执行SQL的时间点-主库执行SQL的时间点。

如果Slave_SQL_Running_State 显示Slave has read all relay log; waiting for more updates，表示从库上IO线程空闲。

对比Master_Log_File+Read_Master_Log_Pos与Relay_Master_Log_File+Exec_Master_Log_Pos，如果相同则代表所有发送到备库的命令都已应用到备库上，如果两者相差不大，则代表备库有一定延迟。

 

监控失效问题：

1、MySQL复制采用主库推送方式，如果主库的dump进程发生异常或主从网络传输发生异常，导致从库未收到主库推送来的信息，主从已出现严重延迟，但Seconds_Behind_Master仍显示为0

2、如果从库IO线程启动后，主库系统时间发生变化，会导致主从延迟不准。

3、如果主库上存在超大事务或DDL操作，只有在主库上提交事务才会发送到从库，而在此之前不会报主从延迟。

4、对于多进程复制，无法使用某个线程的执行情况来反应整个复制的运行情况，因此Seconds_Behind_Master会存在偏差。

 

为解决问题1，MySQL引入下面参数：

slave-net-timeout：在没有得到更多数据之后slave等待的时间，默认值3600s
master-connect-retry：每次和主库建立链接重试的等待时间，默认值为60s
master-retry-count：从库同主库建立链接的重试次数，默认86400次
master_heartbeat_period：当主库没有新二进制日志产生时通知从库的间隔时间,默认值为slave-net-timeout参数的一半。

PS:master_heartbeat_period值可以在CHANGE MASTER时显式指定,在系统表中mysql.slave_master_info中查看。当master_heartbeat_period值为0时，表示禁用该功能。

要解决主从时间差异问题，可以配置相同的时间源并定期进行时间同步(ntpdate)

 

对于超大事务，建议对其进行事务拆分，对于超大表DDL，可以采用pt_osc或ghost方式进行Online DDL。

 

pt-heartbeat工具

由于使用Seconds_Behind_Master无法准确预估出复制延迟情况，因此Percona公司推出pt-heartbeat工具来监控复制延迟。

pt-heartbeat工具实现原理：

1、在主库上创建一张同步表，并定期更新该表数据(时间戳)

2、监控从库上该同步表数据，对比从库上的时间从而计算出复制延迟。

 

优点：复制延迟时间计算准确

缺点：非MySQL原生自带，需要额外部署，并会少量消耗服务器资源