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 条评论
登录 后参与评论

相关文章

来自专栏散尽浮华

Mysql主从同步(1)-主从/主主环境部署梳理

Mysql复制概念说明 Mysql内建的复制功能是构建大型,高性能应用程序的基础。将Mysql的数据分布到多个系统上去,这种分布的机制,是通过将Mysql的某一...

2045
来自专栏铭毅天下

干货 | Elasticsearch集群黄色原因的终极探秘

绿色——最健康的状态,代表所有的主分片和副本分片都可用; 黄色——所有的主分片可用,但是部分副本分片不可用; 红色——部分主分片不可用。(此时执行查询部分数...

330
来自专栏邹立巍的专栏

Linux 进程间通信 : 共享内存(上)

使用文件或管道进行进程间通信会有很多局限性,比如效率问题以及数据处理使用文件描述符而不如内存地址访问方便,于是多个进程以共享内存的方式进行通信就成了很自然要实现...

5052
来自专栏腾讯数据库技术

如何快速删除InnoDB中的大表?

1613
来自专栏王硕

原 Postgres-X2 MPP部署试验

3456
来自专栏大闲人柴毛毛

了解你服务器的心情——top命令详解

top是Linux较为常用的命令,可以监控服务器的CPU、内存、进程的运行情况,话不多说,直接操作。 输入top即可启动: ? 下面我们就来逐一介绍top向我们...

35916
来自专栏java工会

如何恢复oracle中误删除表(drop掉的)?

以下是一个不该发生却经常发生的情况:用户删除了一个非常重要的表 ― 当然是意外地删除 ― 并需要尽快地恢复。(在某些时候,这个不幸的用户可能就是 DBA!)

633
来自专栏腾讯云数据库(TencentDB)

【腾讯云CDB】如何快速删除InnoDB中的大表

在使用MySQL时,如果有大表的存储引擎是InnoDB,并且系统参数innodb_file_per_table设置为1,即每个文件对应一个独立的表空间,当对这些...

4761
来自专栏Hadoop实操

如何在CDH中使用HPLSQL实现存储过程

目前版本的Hive中没有提供类似存储过程的功能,使用Hive做数据应用开发时候,一般有以下两种方法:

6377
来自专栏YoungGy

HIVE入门_2

HIVE 是数据仓库,本质上也是数据库。 数据仓库 概念 就是一个数据库。 数据仓库是一个面向主题的(商品的推荐系统内容是商品的信息)、集成的(分散型地...

2065

扫描关注云+社区