MySQL主从延时这么长,要怎么优化?

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群版 2核4GB 100GB
推荐场景:
搭建个人博客
云数据库 RDS MySQL,高可用版 2核4GB 50GB
简介:

MySQL主从复制读写分离是互联网常见的数据库架构,该架构最令人诟病的地方就是,在数据量较大并发量较大的场景下,主从延时会比较严重。

为什么主从延时这么大?

b53a8c869a8ade8be09f97e06ee96ab8b2c70c4e
:MySQL使用 单线程重放 RelayLog。

应该怎么优化,缩短重放时间?

多线程并行重放RelayLog可以缩短时间。

多线程并行重放RelayLog有什么问题?

5683fb954c9753b6f8da60e1b63c12dc49941b1a
:需要考虑 如何分割RelayLog ,才能够让多个数据库实例多个线程并行重放RelayLog,不会出现不一致。

为什么会出现不一致?

:如果RelayLog随机的分配给不同的重放线程,假设RelayLog中有这样三条串行的修改记录:

update account set money=100 where uid=58;

update account set money=150 where uid=58;

update account set money=200 where uid=58;

如果单线程串行重放:能保证所有从库与主库的执行序列一致。

画外音:最后money都将为200。

如果多线程随机分配重放:多重放线程并发执行这3个语句,谁最后执行是不确定的,最终从库数据可能与主库不同。

画外音:多个从库可能money为100,150,200不确定。

如何分配,多个从库多线程重放,也能得到一致的数据呢?

相同库上的写操作,用相同的线程来重放RelayLog;不同库上的写操作,可以并发用多个线程并发来重放RelayLog。

31c04ff52685a31119d5c4ab1210eec372ab70ae

如何做到呢?

:设计一个哈希算法,hash(db-name) % thread-num,库名hash之后再模上线程数,就能很轻易做到,同一个库上的写操作,被同一个重放线程串行执行。

画外音:不同库上的重放,是并行的,就起到了加速做用。

这个方案有什么不足?

:很多公司对MySQL的使用是“单库多表”,如果是这样的话,仍然只有一个库,还是不能提高RelayLog的重放速度。

启示:将“单库多表”的DB架构模式升级为“多库多表”的DB架构模式

画外音:数据量大并发量大的互联网业务场景,“多库”模式还具备着其他很多优势,例如:

(1)非常方便的实例扩展:DBA很容易将不同的库扩展到不同的实例上;

(2)按照业务进行库隔离:业务解耦,进行业务隔离,减少耦合与相互影响;

(3)非常方便微服务拆分:每个服务拥有自己的实例就方便了;

“单库多表”的场景,多线程并行重放RelayLog还能怎么优化?

:即使只有一个库,事务在主库上也是并发执行的,既然在主库上可以并行执行,在从库上也应该能够并行执行呀?

新思路:将主库上同时并行执行的事务,分为一组,编一个号,这些事务在从库上的回放可以并行执行(事务在主库上的执行都进入到prepare阶段,说明事务之间没有冲突,否则就不可能提交),没错,MySQL正是这么做的。

解法基于GTID的并行复制

从MySQL5.7开始,将组提交的信息存放在GTID中,使用mysqlbinlog工具,可以看到组提交内部的信息:

20181014 23:52 server_id 58 XXX GTID last_committed=0 sequence_numer=1

20181014 23:52 server_id 58 XXX GTID last_committed=0 sequence_numer=2

20181014 23:52 server_id 58 XXX GTID last_committed=0 sequence_numer=3

20181014 23:52 server_id 58 XXX GTID last_committed=0 sequence_numer=4

ce58e2e579795096a63e467bb6db2fd5059b92a8
和原来的日志相比,多了last_committed和sequence_number。

什么是last_committed?

答:它是事务提交时,上次事务提交的编号,如果具备相同的last_committed,说明它们在一个组内,可以并发回放执行

总结

MySQL并行复制,缩短主从同步时延的方法,体现着这样的一些架构思想:

 ●  多线程 是一种常见的缩短执行时间的方法;

画外音:例如,很多crontab可以用多线程,切分数据,并行执行。

 ●  多线程并发分派任务时,必须保证 幂等性 :MySQL提供了“按照库幂等”,“按照commit_id幂等”两种方式,很值得借鉴;

画外音:例如,群消息,可以按照group_id幂等;用户消息,可以按照user_id幂等。

具体到MySQL主从同步延时:

 ●  mysql5.5:不支持并行复制,大伙快 升级MySQL版本
 ●  mysql5.6:按照库并行复制,建议 使用“多库”架构
 ●  mysql5.7:按照GTID并行复制;

思路比结论重要,希望大家有收获。


原文发布时间为:2018-10-15

本文作者:58沈剑

本文来自云栖社区合作伙伴“架构师之路”,了解相关信息可以关注“架构师之路”。

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
25天前
|
关系型数据库 MySQL 数据库
数据迁移脚本优化过程:从 MySQL 到 Django 模型表
在大规模的数据迁移过程中,性能问题往往是开发者面临的主要挑战之一。本文将分析一个数据迁移脚本的优化过程,展示如何从 MySQL 数据库迁移数据到 Django 模型表,并探讨优化前后的性能差异。
|
1天前
|
缓存 关系型数据库 MySQL
MySQL优化
【7月更文挑战第21天】MySQL优化
6 1
|
5天前
|
关系型数据库 MySQL 调度
MySQL高级功能与优化策略深度探索
MySQL高级功能与优化策略深度探索
|
25天前
|
存储 关系型数据库 MySQL
mysql optimizer_switch : 查询优化器优化策略深入解析
mysql optimizer_switch : 查询优化器优化策略深入解析
|
25天前
|
关系型数据库 MySQL 数据库
MySQL索引优化:深入理解索引合并
MySQL索引优化:深入理解索引合并
|
26天前
|
关系型数据库 MySQL 分布式数据库
PolarDB产品使用问题之 MySQL数据库中,执行delete命令删除数据后,存储空间通常不会立即释放,该如何优化
PolarDB产品使用合集涵盖了从创建与管理、数据管理、性能优化与诊断、安全与合规到生态与集成、运维与支持等全方位的功能和服务,旨在帮助企业轻松构建高可用、高性能且易于管理的数据库环境,满足不同业务场景的需求。用户可以通过阿里云控制台、API、SDK等方式便捷地使用这些功能,实现数据库的高效运维与持续优化。
|
27天前
|
存储 关系型数据库 MySQL
Mysql优化之索引相关介绍(笔记)
索引查找从顶层节点开始查找,通过key值,也就是主键的值进行比较,最终定位到存储数据的叶子节点上面,从叶子节点取出响应的数据。
43 0
Mysql优化之索引相关介绍(笔记)
|
9天前
|
关系型数据库 MySQL API
MySQL上亿数据查询优化:实践与技巧
MySQL亿级数据查询优化涉及索引设计、分区表、查询语句优化和数据库架构调整。例如,通过为常用查询列创建索引、使用EXPLAIN分析查询计划、避免全表扫描和SELECT *,以及采用垂直拆分、水平拆分和读写分离来提升性能。分区表能减少查询数据量,API接口测试可验证优化效果。
17 0
|
1月前
|
SQL 缓存 关系型数据库
MySQL慢查询优化实践问答
MySQL慢查询优化实践问答
|
18天前
|
SQL Oracle 关系型数据库
MySQL单表千万级数据查询优化大家怎么说(评论有亮点)
单表千万级数据是MySQL查询的一个坎,可能还不是天花板。“一个人走的慢,一群人走的快”,通过讨论可以发现MySQL千万数据的全貌大概是怎样的。
33 0