昨天帮朋友 看一个问题, 服务器间接主从延迟问题, 延迟时间不固定, 偶现1到2小时随机出现, 每次延迟最高30秒,维持1分钟左右.

主库CPU长期50% 负载不严重, 从库了解到更是没流量没负载.

常见出现主从延迟的场景如下:

1. 主从配置不同, 从库配置查同步慢
2. 从库负载过高, 导致延迟
3. 大事务操作, insert select 最为经典, 还有 就是 for update / delete / update 语句中的 where 使用区间索引.

了解到 可以排除前两样 1 和 2, 排除法定位 查询是否存在慢日志.

使用谷歌的GCP云服务, 内部提供了个查询工具, 通过平均执行时间倒序 找出问题的

1) 逐一SQL排查 发现 发现 存在 insert select 语句 是个大问题
2) 又发现两个 delete 语句 where 条件是 id <= $id and type=1 and status=1, id 是主键 type不是1就是2 虽然 type status 存在索引,但这个delete 语句 用到的还是 主键索引! 索引导致区间锁的问题.

解决方案:

修改 1) 2) 的SQL语句, 改为

1. 查询出需要删除的表 500条
2. 事务开启
3. 批量插入500条到新表
4. 使用 主键ID, delete from where id in () 批量删除500条 
5. 事务提交