MySQL复制延迟优化的方法论

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
云数据库 RDS PostgreSQL,高可用系列 2核4GB
简介:

一、MySQL为什么会延迟

数据延迟: 是指master执行了N个事务,slave却只执行了N-M个事务,说明master和slave之间产生了延迟

延迟原因:延迟的原因很多种,大部分情况下是 slave的处理能力跟不上master导致

接下来,我们从各种角度分析下延迟的原因

1.1 MySQL复制的架构

delay_1

通过架构图,可以直观的看到数据延迟的点有哪些,当然也就可以知道如何优化了

1.2 大事务导致的延迟

大家都知道,binlog的写入时机是在commit的时候,redo的写入时机是在事务执行阶段就开始。

Oracle是通过物理复制,我们姑且认为是redo的复制,因为redo是事务执行阶段就开始写入的,所以,oracle的复制几乎没有延迟

MySQL是基于binlog复制的,如果有一个非常大的事务,如果需要1个小时,那么master在1小时候后才会生成binlog,而此时,slave就比master慢了至少1个小时,还不算是binlog传输时间

这是第一种延迟原因,破解方法后面说

PS: DDL虽然不是事务,但是特性跟大事务一样,都是在master上执行了一个巨大无比的操作才写的binlog

1.3 IO线程导致的延迟

根据复制的架构,Master写完binlog后,需要通过网络传输给slave(这部分我们需要网络的支持)
然后呢,IO thread会将binlog写到slave的relay log中,这部分工作由IO thread完成

好了,这里我们分析下瓶颈:

  1. io thread 是单线程的
  2. io thread 写入 relay log的速度

经过分析以及大量的实战,IO thread并不是我们的瓶颈,因为relay log是顺序的写入,非常快,几乎碰不到瓶颈

1.4 SQL线程导致的延迟

master 上面的事务是可以进行并发的,然后binlog传输到slave后,slave是却以单线程的模式读取和执行relay log
这是典型的消费能力不足

1.5 网络问题导致的延迟

网络问题不用多说了吧,如果要复制良好,一个稳定的网路环境是在所难免的

1.6 硬件问题导致的延迟

如果master是SSD,但是slave还是机械硬盘,这样的架构存在延迟也不足为奇

二、延迟场景的解决方案

2.1 DDL

2.1.1 ddl的最佳实践
  1. 通过pt-osc 或者 gh-ost 来让ddl拆分成一个个小事务,并且还有流控功能
  2. 在slave上先ddl,然后master-slave切换,然后再old master上进行ddl,从而完美的解决了这个问题

2.2 大事务

2.2.1 大事务拆小事务

如果说大事务对于binlog的产生有极大的影响,那么我们认为定义小事务,大事务不允许执行

有大事务的监控,可以基于时间,可以基于数据量,监控到不符合规范的trx自动kill

2.3 大量并发事务

2.3.1 调整安全参数

sync_binlog = 0 && innodb_flush_trx_commit = 0 可以极大的提高事务处理的吞吐量,因为IO fsync的次数变少了,可以非常有效的降低数据延迟

风险:如果slave挂了,需要重做slave

2.3.2 MTS(enhanced multi-threaded slave)

之前有深入讨论过MTS的文章,它主要的功能就是让slave拥有比master更快的并行能力,从而有效的让延迟缩短,甚至无延迟

终极大招

半同步: 半同步可以让延迟为0,但是半同步有自动切换为异步复制的可能

全同步: MySQL的group replication 就是这类的代表,这个话题以后再聊

最后,以上就是关于MySQL延迟优化的方法,几乎涵盖了90%的方案,如果大家还有更好的方案,不妨拿出来大家一起探讨

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
26天前
|
SQL 缓存 关系型数据库
MySQL 慢查询是怎样优化的
本文深入解析了MySQL查询速度变慢的原因及优化策略,涵盖查询缓存、执行流程、SQL优化、执行计划分析(如EXPLAIN)、查询状态查看等内容,帮助开发者快速定位并解决慢查询问题。
|
3天前
|
存储 SQL 关系型数据库
MySQL 核心知识与索引优化全解析
本文系统梳理了 MySQL 的核心知识与索引优化策略。在基础概念部分,阐述了 char 与 varchar 在存储方式和性能上的差异,以及事务的 ACID 特性、并发事务问题及对应的隔离级别(MySQL 默认 REPEATABLE READ)。 索引基础部分,详解了 InnoDB 默认的 B+tree 索引结构(多路平衡树、叶子节点存数据、双向链表支持区间查询),区分了聚簇索引(数据与索引共存,唯一)和二级索引(数据与索引分离,多个),解释了回表查询的概念及优化方法,并分析了 B+tree 作为索引结构的优势(树高低、效率稳、支持区间查询)。 索引优化部分,列出了索引创建的六大原则
|
5月前
|
SQL 关系型数据库 MySQL
MySQL进阶突击系列(07) 她气鼓鼓递来一条SQL | 怎么看执行计划、SQL怎么优化?
在日常研发工作当中,系统性能优化,从大的方面来看主要涉及基础平台优化、业务系统性能优化、数据库优化。面对数据库优化,除了DBA在集群性能、服务器调优需要投入精力,我们研发需要负责业务SQL执行优化。当业务数据量达到一定规模后,SQL执行效率可能就会出现瓶颈,影响系统业务响应。掌握如何判断SQL执行慢、以及如何分析SQL执行计划、优化SQL的技能,在工作中解决SQL性能问题显得非常关键。
|
2月前
|
存储 SQL 关系型数据库
京东面试:mysql深度分页 严重影响性能?根本原因是什么?如何优化?
京东面试:mysql深度分页 严重影响性能?根本原因是什么?如何优化?
京东面试:mysql深度分页 严重影响性能?根本原因是什么?如何优化?
|
3月前
|
监控 Java 关系型数据库
Spring Boot整合MySQL主从集群同步延迟解决方案
本文针对电商系统在Spring Boot+MyBatis架构下的典型问题(如大促时订单状态延迟、库存超卖误判及用户信息更新延迟)提出解决方案。核心内容包括动态数据源路由(强制读主库)、大事务拆分优化以及延迟感知补偿机制,配合MySQL参数调优和监控集成,有效将主从延迟控制在1秒内。实际测试表明,在10万QPS场景下,订单查询延迟显著降低,超卖误判率下降98%。
117 5
|
4月前
|
存储 关系型数据库 MySQL
MySQL细节优化:关闭大小写敏感功能的方法。
通过这种方法,你就可以成功关闭 MySQL 的大小写敏感功能,让你的数据库操作更加便捷。
293 19
|
5月前
|
关系型数据库 MySQL 数据库
RDS用多了,你还知道MySQL主从复制底层原理和实现方案吗?
随着数据量增长和业务扩展,单个数据库难以满足需求,需调整为集群模式以实现负载均衡和读写分离。MySQL主从复制是常见的高可用架构,通过binlog日志同步数据,确保主从数据一致性。本文详细介绍MySQL主从复制原理及配置步骤,包括一主二从集群的搭建过程,帮助读者实现稳定可靠的数据库高可用架构。
332 9
RDS用多了,你还知道MySQL主从复制底层原理和实现方案吗?
|
5月前
|
SQL 关系型数据库 MySQL
基于SQL Server / MySQL进行百万条数据过滤优化方案
对百万级别数据进行高效过滤查询,需要综合使用索引、查询优化、表分区、统计信息和视图等技术手段。通过合理的数据库设计和查询优化,可以显著提升查询性能,确保系统的高效稳定运行。
184 9
|
5月前
|
关系型数据库 MySQL 数据库
从MySQL优化到脑力健康:技术人与效率的双重提升
聊到效率这个事,大家应该都挺有感触的吧。 不管是技术优化还是个人状态调整,怎么能更快、更省力地完成事情,都是我们每天要琢磨的事。
115 23
|
5月前
|
监控 关系型数据库 MySQL
MySQL和SQLSugar百万条数据查询分页优化
在面对百万条数据的查询时,优化MySQL和SQLSugar的分页性能是非常重要的。通过合理使用索引、调整查询语句、使用缓存以及采用高效的分页策略,可以显著提高查询效率。本文介绍的技巧和方法,可以为开发人员在数据处理和查询优化中提供有效的指导,提升系统的性能和用户体验。掌握这些技巧后,您可以在处理海量数据时更加游刃有余。
534 9

推荐镜像

更多