mysql主从同步延迟优化大全

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了mysql主从同步延迟优化大全，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含3410字，纯文字阅读大概需要5分钟。

内容图文

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

创建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

内容总结

以上是互联网集市为您收集整理的mysql主从同步延迟优化大全全部内容，希望文章能够帮你解决mysql主从同步延迟优化大全所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端