Mysql主从延迟解决办法

1.MIXED模式无索引或SQL慢

在从库上pager grep -v Sleep;show processlist 查看到正在执行的SQL。

解决方法:

a. SQL比较简单, 则检查是否缺少索引,并添加索引。比如update操作where条件没有索引。

b. 另一类是 insert into select from的语句,如果select 里包含group by,多表关联,可能效率会比较低。这类可以到主库把binlog_format改成row。

2. 主库上有大事务,导致从库延时 现象解析binlog 发现类似于下图的情况看

解决方法:

与开发沟通,增加缓存,异步写入数据库,减少直接对db的大量写入。

3. 主库写入频繁,从库压力跟不上导致延时

此类原因的主要现象是数据库的IUD(插改删) 操作非常多,slave由于sql_thread单线程的原因追不上主库。 解决方法:

a. 升级从库的硬件配置,比如ssd、fio

b. 设置日志刷新频率:

set global innodb_flush_log_at_trx_commit=0;

setglobal sync_binlog=0;

  • innodb_flush_log_at_trx_commit=0每秒将log_buffer刷新到logfile,并且将日志同步刷新到磁盘。该模式下,在事务提交的时候,不会主动触发写入磁盘的操作。
  • innodb_flush_log_at_trx_commit=1每次commit将log_buffer刷新到logfile,并且将日志同步刷新到磁盘
  • innodb_flush_log_at_trx_commit=2 每次commit将log_buffer刷新到logfile,但是flush(刷到磁盘)操作并不会同时进行,每秒将日志刷新到磁盘。

4. 数据库中存在大量myisam表,在备份的时候导致slave延迟

由于xtrabackup工具备份到最后会执行flash tables with read lock,对数据库进行锁表以便进行一致性备份,然后对于myisam表锁,会阻碍salve_sql_thread停滞运行进而导致hang。

该问题目前的比较好的解决方式是修改表结构为innodb存储引擎的表。

原文发布于微信公众号 - MYSQL轻松学(learnmysql)

原文发表时间:2016-09-21

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏FreeBuf

搭建开源入侵检测系统Snort,并实现与防火墙联动

之前做入侵检测与防火墙联动时,发现这方面资料较少,研究成功后拿出来和大家分享一下。 Snort作为一款优秀的开源主机入侵检测系统,在windows和Linux平...

7535
来自专栏Elasticsearch实验室

Mysql数据导入Elasticsearch最佳实践

  Elasticsearch(ES)可用于全文检索、日志分析、指标分析、APM等众多场景,而且搭建部署容易,后期弹性扩容、故障处理简单。ES在一定程度上实...

1.1K8
来自专栏蔡鹏的专栏

开源分布式监控系统 OWLv5.0.0 安装部署完整手册

OWL 是TalkingData公司推出的一款开源分布式监控系统 . 目前使用OWL监控了二十几台服务器,便捷很多 Go语言开发,部署维护简单

4988
来自专栏同步博客

Memcached与MySQL数据同步

  在生产环境中,我们经常使用MySQL作为应用的数据库。但是随着用户的增多数据量的增大,我们将会自然而然的选择Memcached作为缓存数据库,从而减小MyS...

1152
来自专栏腾讯云Elasticsearch Service

Elasticsearch最佳实践从Mysql到Elasticsearch

  Elasticsearch(ES)可用于全文检索、日志分析、指标分析、APM等众多场景,而且搭建部署容易,后期弹性扩容、故障处理简单。ES在一定程度上实...

6821
来自专栏PHP在线

手把手教会你小程序登录鉴权

4604
来自专栏逸鹏说道

SQL Server安全(2/11):身份验证(Authentication)

在保密你的服务器和数据,防备当前复杂的攻击,SQL Server有你需要的一切。但在你能有效使用这些安全功能前,你需要理解你面对的威胁和一些基本的安全概念。这篇...

3398
来自专栏zhisheng

《从0到1学习Flink》—— Flink 配置文件详解

前面文章我们已经知道 Flink 是什么东西了,安装好 Flink 后,我们再来看下安装路径下的配置文件吧。

1090
来自专栏数据和云

Oracle 12.2新特性掌上手册 - 第五卷 RAC and Grid

编辑手记:RAC是Oracle最重要的高可用架构之一,具有扩展性良好、实现负载均衡等多维度的优势,Oracle RAC提供了相应的集群软件和存储管理软件,今天我...

2854
来自专栏云飞学编程

大神说你学会这个爬虫高级技巧,各种网站随便你爬!

1451

扫码关注云+社区