首页 / MYSQL / mysql主从同步延迟优化大全
mysql主从同步延迟优化大全
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了mysql主从同步延迟优化大全,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含4537字,纯文字阅读大概需要7分钟。
内容图文
![mysql主从同步延迟优化大全](/upload/InfoBanner/zyjiaocheng/509/265f3b1355364909a9b6cfbf4cc5dca8.jpg)
由于我没有将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的碎片,提升空间和内存的使用。
mysql主从同步延迟优化大全
标签:行操作 查看连接 并发 条目 bin ble 大全 column value
本文系统来源:https://www.cnblogs.com/sqlservertongbu/p/11013648.html
内容总结
以上是互联网集市为您收集整理的mysql主从同步延迟优化大全全部内容,希望文章能够帮你解决mysql主从同步延迟优化大全所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。