mysql多线程复制延迟_社区投稿 | MySQL 主从复制延时常见场景及分析改善

原创： 杨奇龙

作者简介

杨奇龙，网名“北在南方”，7年DBA老兵，目前任职于杭州有赞科技DBA，主要负责数据库架构设计和运维平台开发工作，擅长数据库性能调优、故障诊断。

一 序言

在运维MySQL数据库时,DBA会接收到比较多关于主备延时的报警:

check_ins_slave_lag (err_cnt:1)critical-slavelag on ins:3306=39438

相信slave延迟是MySQL DBA遇到的一个老生常谈的问题了。我们先来分析一下slave延迟带来的风险：

异常情况下,主从HA无法切换，HA 软件需要检查数据的一致性，延迟时，主备不一致

备库复制hang会导致备份失败(flush tables with read lock会900s超时)

以slave为基准进行的备份，数据不是最新的，而是延迟

本文主要探讨如何解决 ，如何规避slave延迟的问题，接下来我们要分析一下导致备库延迟的几种原因。

二 slave延迟的场景以及解决方法

1 无主键、无索引或索引区分度不高

有如下特征

a. show slave status 显示position一直没有变

b. show open tables 显示某个表一直是 in_use 为 1

c. show create table 查看表结构可以看到无主键，或者无任何索引，或者索引区分度很差。

解决方法：

a. 找到表区分度比较高的几个字段, 可以使用这个方法判断：

select count(*) from xx;

select count(*) from (select distinct xx from xxx) t;

如果2个查询count(*)的结果差不多，说明可以对这些字段加索引

b. 备库stop slave;

可能会执行比较久，因为需要回滚事务。

c. 备库

set global slave_rows_search_algorithms='TABLE_SCAN,INDEX_SCAN,HASH_SCAN';

或者

set sql_log_bin=0;

alter table xx add key xx(xx);

d. 备库start slave

如果是innodb,可以通过show innodb status来查看 rows_inserted,updated,deleted,selected这几个指标来判断。

如果每秒修改的记录数比较多，说明复制正在以比较快的速度执行。

其实针对无索引的表，可以直接调整从库上的参数slave