mysql主从同步延迟优化大全

mysql> create database fafa;

Query OK, 1 row affected (0.01 sec)

mysql> use fafa

Database changed

mysql> create table test(jj int,kk varchar(10));

Query OK, 0 rows affected (0.02 sec)

接下来我们在主库循环插入数据实验，然后看看进程状态，和同步情况

创建procedure

delimiter //

create procedure fafa()

begin

declare num int;

set num=1;

while num < 8000000 do

insert into test(jj,kk) values(num,'fafa');

set num=num+1;

end while;

end

//

之后我们要调用这个procedure，才会插入数据

call fafa();    //

由于我没有将delimiter’改回来，所以输入‘；’后并没有执行，还需要//

注意这里有一个现象，出现了主从很大的延时，这里我们进行逐个排查。总结排查方法

情况1：

只读实例规格配置过小导致延迟

这类延迟场景的出现往往是主节点拥有较大规格的配置，而只读节点却购买了一个最小规格的配置（例如2G内存/200 IOPS）。

     原理解析：只读节点的数据为了和主节点保持同步，采用了MySQL binlog复制技术，由一个IO线程和一个SQL线程来完成，IO线程负责将主库的binlog拉取到只读节点，SQL线程负责消费这些binlog日志，这两个线程会消耗掉只读节点的IO资源，所以当只读节点IOPS配置不够的时候，则会导致只读节点的数据出现延迟

解决办法：

      升级只读实例的规格（可以参考主库此时的IOPS的消耗情况），防止由于只读实例的规格较小导致了数据延迟。

具体只需只读实例节点的配置大于或者等于主节点的配置即可。

情况2：

主库的TPS过高导致只读节点延迟

      原理解析：由于只读节点与主库的同步采用的是单线程同步，而主库的压力是并发多线程写入，这样势必会导致只读节点的数据延迟

解决办法：

开启只读节点的并行复制是解决这一问题的根本方法，想彻底解决还得排查业务写入压力是否正常，适当对业务进行优化或者拆分，保证主库的TPS不会导致slave出现延迟。

拓展：

在MySQL5.6中，引入了并发复制，这个并发复制是数据库级别的，这意味着一个SQL线程可以处理一个数据库的连续事务，而不用等待其它数据库完成。这个版本的并发复制，可以理解成一个数据库一个SQL线程。其与并发有关的参数如下：

slave_parallel_workers           // worker 线程个数

slave-checkpoint-group           // 隔多少个事务做一次 checkpoint

slave-checkpoint-period          // 隔多长时间做一次 checkpoint

slave-pending-jobs-size-max      // 分发给worker的、处于等待状态的event的大小上限

 

MySQL5.6基于DATABASE级别的并发复制可以解决业务表放在不同的database下同步延迟的问题，但是在实际生产中大部分表还是放在同一个库中的，这种情况即使设置slave_parallel_workers大于0，也无法进行并发。在高并发的情况下，依然会造成主从复制延迟，所以说MySQL 5.7版本才真正支持“真正”的并行复制功能

 

在MySQL5.7中，引入了新的并发复制方法，基于LOGICAL_CLOCK的并发复制，可以支持在一个database中，并发执行relaylog中的事务。

相同的二进制日志组在master上提交并行应用到slave节点上，没有跨数据库的限制，并且不需要把数据分割到多个数据库。

要实现这个功能，需要在master节点标记binlog中提交的事务哪些是可以并发执行，虽然的MySQL5.6中已经引入binarylog group commit,但是没有将可并发的事务标记出来。

可以通过此命令来查看：mysqlbinlog-vvv mysql-bin.000106 | grep -i last_commit

在MySQL5.7中，已经解决了主从复制延迟的问题，具体配置参数如下：

slave-parallel-type=LOGICAL_CLOCK

slave-parallel-workers=16

master_info_repository=TABLE

relay_log_info_repository=TABLE

relay_log_recovery=ON

情况3：

主库的DDL(alter、drop、repair、create)导致只读节点延迟

    可能1：只读节点与主库的DDL同步是串行进行的，如果DDL操作在主库执行时间很长，那么同样在备库也会消耗同样的时间，比如在主库对一张500W的表添加一个字段耗费了10分钟，那么在只读节点上也同样会耗费10分钟，所以只读节点会延迟600S，其他常见操作比如：

mysql> alter table test add column nn varchar(10);

mysql> alter table test add index(jj);

    可能2：只读节点上有一个执行时间非常长的的查询正在执行，那么这个查询会堵塞来自主库的DDL，读节点表被锁，直到查询结束为止，进而导致了只读节点的数据延迟。在只读节点上可以通过执行show processlist命令查看连接的状态处于: Waiting for table metadata lock

解决办法：

对于可能1，只能说执行操作之前对可能带来的影响要有考量，对于情况2，可以kill掉只读节点上的大查询进行，就可以恢复只读节点与主节点的数据同步

情况4：

主库执行大事务导致延迟

     主库执行了一条insert … select非常大的插入操作，该操作产生了近几百G的binlog文件传输到只读节点，进而导致了只读节点出现应用binlog延迟。

解决办法：

     将大事务拆分成为小事务进行排量提交，这样只读节点就可以迅速的完成事务的执行，不会造成数据的延迟。

情况5：

无主键的表进行DML操作导致延迟

mysql> update test set kk='fafa01';

由于表中没有主键，所以导致了每一个事务条目的更新都是全表扫描，如果表中很很多的数据，则备库执行该更新的事务条目的时候，就会出现很多的全表扫描更新；进一步说明就是，由于表中没有主键，在ROW模式下，每删一条数据都会做全表扫，也就是说一条delete，如果删了10条，会做10次全表扫，所以slave会一直卡住；

拓展：

       主键对于innodb来说，是非常重要的，每张表的设计的时候，都应该把主键默认的加上，不管你需不需要他，而且主键的设计最好选择自增型的主键，这里也可以略提一下自增主键的好处：

a.自增型主键以利于插入性能的提高；

b.自增型主键设计(int,bigint)可以降低二级索引的空间，提升二级索引的内存命中率；

c.自增型的主键可以减小page的碎片，提升空间和内存的使用。