mysql relaylog 慢_Mysql解决主从慢同步问题

Mysql解决主从慢同步问题

一.简介

一般主从复制，有三个线程参与，都是单线程：Binlog Dump(主) ----->IO Thread (从) -----> SQL Thread(从)。复制出现延迟一般出在两个地方

1)SQL线程忙不过来(可能需要应用数据量较大，可能和从库本身的一些操作有锁和资源的冲突；主库可以并发写，SQL线程不可以；一个大的sql语句导致执行很慢；)

2)网络抖动导致IO线程复制延迟(次要原因)。

SQL thread在执行IO thread dump下来的relay log的时间差。大家都知道relay log中event记录的时间戳是主库上的时间戳，而SQL thread的时间戳是从库上的，也就是说，如果主库和从库的时间是一致的，那么这个SBM代表的确实是从库延后主库的一个时间差。但是如果主库和从库的时间不是一致的，那么这个SBM的意义就基本不存在了。将主库时间调快1小时，那从库默认慢一小时。

为何有延迟

1.网络延迟

若主从之间网络延迟到，会造成sql线程无法实时将主的binlog日志复制过来。

2.机器性能差

若主用的固态硬盘，从用的机械硬盘，那读取速度自然不一样，会造成主写入很快，从在慢慢读取，这样就不适合读写分离了。

3.高负载

若从机器还安装了别的服务，用top可以看出是否有其它进程在占用资源，导致从性能下降。

4.磁盘负载

用iotop可以看到当前磁盘的负载，若正在复制某些东西，会导致将主的binlog复制过来了，但写入到从mysql中会很慢，数据不一致。

5.是否经常会有大事务？

这个可能DBA们会遇到比较多，比如在RBR模式下，执行带有大量的Delete操作，或者在MBR模式下删除时添加了不确定语句(类似limit)或一个表的Alter操作等，都会导致延迟情况的发生。

这种可通过查看Processlist相关信息，以及使用mysqlbinlog查看binlog中的SQL就能快速进行确认。这个设想也被排除。

6.死锁

锁冲突问题也可能导致从机的SQL线程执行慢，比如从机上有一些select .... for update的SQL，或者使用了MyISAM引擎等。此类问题，可以通过抓去Processlist以及查看information_schema下面和锁以及事务相关的表来查看。

二.观察

在主上用SHOW MASTER STATUS;查看最新的binlog日志，在从上用show slave status\G;查看Master_Log_File，如果是一样的，说明sql线程将主的binlog日志都复制过来了，这是没延迟的。如果Seconds_Behind_Master是0则IO线程将同步过来的binlog日志都加载了，那延迟为0。

seconds_behind_master如果一直为0，突然就很高，那是因为主库在执行一个大的事件，当事件执行完成后从才开始复制，sbm会突然很高。

三.解决办法

参数

关闭binlog日志可以减轻从库的负载

配置文件添加如下，将不缓冲直接写入，从而加速性能

sync_binlog=0

innodb_flushlog

innodb_flush_log_at_trx_commi=0

多线程

5.6开始MySQL正式支持多线程复制，如下命令查看有多少个线程在同步。

show variables like '%slave_parallel%'

slave_parallel_type

为DATABASE时，基于数据库的并发，也就是每一个数据库都有一个线程去同步，如果只有一个数据库，那其它线程不工作。不同库下的表并发提交时的数据不会相互影响，即slave节点可以用对relay log中不同的库各分配一个类似SQL功能的线程，来重放relay log中主库已经提交的事务，保持数据与主库一致。

为LOGICAL_CLOCK时，则可以一个数据库一个多线程同步。一个组提交的事务都是可以并行回放(配合binary log group commit)。

slave_parallel_workers

代表启动多少个线程用于同步，0就是默认1个。

静态设置：

slave_parallel_type='LOGICAL_CLOCK'

slave_parallel_workers=8

动态设置：

SET GLOBAL slave_parallel_type='LOGICAL_CLOCK';

SET GLOBAL slave_parallel_workers=8;

组提交

在5.7中，多线程复制的功能有很很大的改善，支持LOGICAL_CLOCK的方式，在这种方式下，并发执行的多个事务只要能在同一时刻commit，就说明线程之间没有锁冲突，那么Master就可以将这一组的事务标记并在slave机器上安全的进行并发执行。

因此，可以尽可能地使所有线程能在同一时刻提交，这样就能很大程度上提升从机的执行的并行度，从而减少从机的延迟。

有了这个猜想后，很自然想到了人为控制尽可能多地使所有线程在同一时刻提交，其实官方已经给我们提供了类似的参数，参数如下：

binlog_group_commit_sync_delay

备注：这个参数会对延迟SQL的响应，对延迟非常敏感的环境需要特别注意，单位是微秒。

由于是监控的DB，主要是load数据，然后进行展示，1秒左右的导入延迟对业务没什么影响，因此将两个参数调整为：

SET GLOBAL binlog_group_commit_sync_delay = 1000000;

SET GLOBAL binlog_group_commit_sync_no_delay_count = 20;

备注：这两个参数请根据业务特性进行调整，以免造成线上故障。

为了防止导入SQL堆积，设置SET GLOBAL binlog_group_commit_sync_no_delay_count为20，在达到20个事务时不管是否达到了1秒都进行提交，来减少对业务的影响。