MySQL实战45讲
林晓斌
网名丁奇,前阿里资深技术专家
立即订阅
43178 人已学习
课程目录
已完结 48 讲
0/4登录后,你可以任选4讲全文学习。
开篇词 (1讲)
开篇词 | 这一次,让我们一起来搞懂MySQL
免费
基础篇 (8讲)
01 | 基础架构:一条SQL查询语句是如何执行的?
02 | 日志系统:一条SQL更新语句是如何执行的?
03 | 事务隔离:为什么你改了我还看不见?
04 | 深入浅出索引(上)
05 | 深入浅出索引(下)
06 | 全局锁和表锁 :给表加个字段怎么有这么多阻碍?
07 | 行锁功过:怎么减少行锁对性能的影响?
08 | 事务到底是隔离的还是不隔离的?
实践篇 (37讲)
09 | 普通索引和唯一索引,应该怎么选择?
10 | MySQL为什么有时候会选错索引?
11 | 怎么给字符串字段加索引?
12 | 为什么我的MySQL会“抖”一下?
13 | 为什么表数据删掉一半,表文件大小不变?
14 | count(*)这么慢,我该怎么办?
15 | 答疑文章(一):日志和索引相关问题
16 | “order by”是怎么工作的?
17 | 如何正确地显示随机消息?
18 | 为什么这些SQL语句逻辑相同,性能却差异巨大?
19 | 为什么我只查一行的语句,也执行这么慢?
20 | 幻读是什么,幻读有什么问题?
21 | 为什么我只改一行的语句,锁这么多?
22 | MySQL有哪些“饮鸩止渴”提高性能的方法?
23 | MySQL是怎么保证数据不丢的?
24 | MySQL是怎么保证主备一致的?
25 | MySQL是怎么保证高可用的?
26 | 备库为什么会延迟好几个小时?
27 | 主库出问题了,从库怎么办?
28 | 读写分离有哪些坑?
29 | 如何判断一个数据库是不是出问题了?
30 | 答疑文章(二):用动态的观点看加锁
31 | 误删数据后除了跑路,还能怎么办?
32 | 为什么还有kill不掉的语句?
33 | 我查这么多数据,会不会把数据库内存打爆?
34 | 到底可不可以使用join?
35 | join语句怎么优化?
36 | 为什么临时表可以重名?
37 | 什么时候会使用内部临时表?
38 | 都说InnoDB好,那还要不要使用Memory引擎?
39 | 自增主键为什么不是连续的?
40 | insert语句的锁为什么这么多?
41 | 怎么最快地复制一张表?
42 | grant之后要跟着flush privileges吗?
43 | 要不要使用分区表?
44 | 答疑文章(三):说一说这些好问题
45 | 自增id用完怎么办?
特别放送 (1讲)
直播回顾 | 林晓斌:我的 MySQL 心路历程
结束语 (1讲)
结束语 | 点线网面,一起构建MySQL知识网络
MySQL实战45讲
登录|注册

26 | 备库为什么会延迟好几个小时?

林晓斌 2019-01-11
在上一篇文章中,我和你介绍了几种可能导致备库延迟的原因。你会发现,这些场景里,不论是偶发性的查询压力,还是备份,对备库延迟的影响一般是分钟级的,而且在备库恢复正常以后都能够追上来。
但是,如果备库执行日志的速度持续低于主库生成日志的速度,那这个延迟就有可能成了小时级别。而且对于一个压力持续比较高的主库来说,备库很可能永远都追不上主库的节奏。
这就涉及到今天我要给你介绍的话题:备库并行复制能力。
为了便于你理解,我们再一起看一下第 24 篇文章《MySQL 是怎么保证主备一致的?》的主备流程图。
图 1 主备流程图
谈到主备的并行复制能力,我们要关注的是图中黑色的两个箭头。一个箭头代表了客户端写入主库,另一箭头代表的是备库上 sql_thread 执行中转日志(relay log)。如果用箭头的粗细来代表并行度的话,那么真实情况就如图 1 所示,第一个箭头要明显粗于第二个箭头。
在主库上,影响并发度的原因就是各种锁了。由于 InnoDB 引擎支持行锁,除了所有并发事务都在更新同一行(热点行)这种极端场景外,它对业务并发度的支持还是很友好的。所以,你在性能测试的时候会发现,并发压测线程 32 就比单线程时,总体吞吐量高。
而日志在备库上的执行,就是图中备库上 sql_thread 更新数据 (DATA) 的逻辑。如果是用单线程的话,就会导致备库应用日志不够快,造成主备延迟。
取消
完成
0/1000字
划线
笔记
复制
© 版权归极客邦科技所有,未经许可不得传播售卖。 页面已增加防盗追踪,如有侵权极客邦将依法追究其法律责任。
该试读文章来自付费专栏《MySQL实战45讲》,如需阅读全部文章,
请订阅文章所属专栏。
立即订阅
登录 后留言

精选留言(61)

  • 老杨同志 置顶
    尝试回答 慧鑫coming 的问题。
    老师图片的步骤有下面5步
    1 redo log prepare write
    2 binlog write
    3 redo log prepare fsync
    4 binlog fsync
    5 redo log commit write

    1)如果更新通一条记录是有锁的,只能一个事务执行,其他事务等待锁。

    2)第4步的时候会因为下面两个参数,等其他没有锁冲突的事务,一起刷盘,此时一起执行的事务拥有相同的commit_id
    binlog_group_commit_sync_delay
    binlog_group_commit_sync_no_delay_count

    3)执行步骤5后,释放锁,等待锁的事务开始执行。

    所以对同一行更新的事务,不可能拥有相同的commit_id

    作者回复: 👍,你比我回复得详细,顶起

    2019-01-11
    25
  • 长杰 置顶
    举个例子,一个事务更新了表 t1 和表 t2 中的各一行,如果这两条更新语句被分到不同 worker 的话,虽然最终的结果是主备一致的,但如果表 t1 执行完成的瞬间,备库上有一个查询,就会看到这个事务“更新了一半的结果”,破坏了事务逻辑的原子性。

    老师这块不太明白,备库有查询会看到更新了一半的结果,t1的worker执行完了更新会commit吗?如果不commit,备库查询应该看不到吧?如果commit,就破坏了事物的原子性,肯定是有问题的。

    作者回复: 应该是说,它迟早要commit,但是两个worker是两个线程,没办法约好“同时提交”,这样就有可能出现一个先提交一个后提交。
    这两个提交之间的时间差,就能被用户看到“一半事务”,好问题

    2019-01-11
    2
    9
  • jike 置顶
    老师您好,开启并行复制后,事务是按照组来提交的,从库也是根据commit_id来回放,如果从库也开启binlog的话,那是不是存在主从的binlog event写入顺序不一致的情况呢?

    作者回复: 是有可能binlog event写入顺序不同的,好问题

    2019-01-15
    5
  • HuaMax
    课后题。关键点在于主库单线程,针对三种不同的策略,COMMIT_ORDER:没有同时到达redo log的prepare 状态的事务,备库退化为单线程;WRITESET:通过对比更新的事务是否存在冲突的行,可以并发执行;WRITE_SESSION:在WRITESET的基础上增加了线程的约束,则退化为单线程。综上,应选择WRITESET策略

    作者回复: 准确👍

    2019-01-12
    24
  • 每天晒白牙
    我是做java的,看老师的这个专栏,确实挺吃力的,老师专栏的干货太多了,下面的留言也是相当有水平,质量都很高,互动也好,应该是好多DBA吧,做java的我,看的头大

    作者回复: 这几篇偏深,但确实是大家在使用的时候需要了解的,
    到30篇后面的文章会偏应用哈

    2019-01-13
    2
    11
  • 某、人
    总结下多线程复制的流程,有不对之处请老师指出:
    双1,配置为logical_clock,假设有三个事务并发执行也已经执行完成(都处于prepare阶段)
    1.三个事务把redo log从redo log buffer写到fs page cache中
    2.把binlog_cache flush到binlog文件中,最先进入flush队列的为leader,
    其它两个事务为follower.把组员编号以及组的编号写进binlog文件中(三个事务为同一组).
    3.三个事务的redo log做fsync,binlog做fsync.
    4.dump线程从binlog文件里把binlog event发送给从库
    5.I/O线程接收到binlog event,写到relay log中
    6.sql thread读取relay log,判断出这三个事务是处于同一个组,
    则把这三个事务的event打包发送给三个空闲的worker线程(如果有)并执行。

    配置为writeset的多线程复制流程:
    1.三个事务把redo log从redo log buffer写到fs page cache中
    2.把binlog_cache flush到binlog文件中,根据表名、主键和唯一键(如果有)生成hash值(writeset),保存到hash表中
    判断这三个事务的writeset是否有冲突,如果没有冲突,则视为同组,如果有冲突,则视为不同组.
    并把把组员编号以及组的编号写进binlog文件中
    (不过一个组的事务个数也不是无限大,由参数binlog_transaction_dependency_history_size决定组内最多事务数)
    3.然后做redo log和binlog的fsync
    4.dump线程从binlog文件里把binlog event发送给从库
    5.I/O线程接收到binlog event,写到relay log中
    6.sql thread读取relay log,如果是同一个组的事务,则把事务分配到不同的worker线程去应用relay log.
    不同组的事务,需要等到上一个组的事务全部执行完成,才能分配worker线程应用relay log.

    老师我有几个问题想请教下:
    1.在备库是单线程下,second_behind_master是通过计算T3-T1得到,
    在多线程的情况下,是怎么计算出second_behind_master的值?用的是哪一个事务的时间戳?
    2.多线程复制下,如果从库宕机了,是不是从库有一个记录表记录那些事务已经应用完成,
    恢复的时候,只需要恢复未应用的事务.
    3.binlog延迟sync的两个参数,是延迟已经flush未sync时间。意思是让事务组占用flush时间更长,
    之后的事务有更多的时间,从binlog cache进入到flush队列,使得组员变多,起到从库并发的目的
    因为我理解的是加入到组是在binlog cache flush到binlog文件之前做的,如果此时有事务正在flush,
    未sync,则后面的事务必须等待。不知道理解得对不



    作者回复: 上面的描述部分,writeset的多线程复制流程里面,这段需要修改下:
    『2.把binlog_cache flush到binlog文件中,根据表名、主键和唯一键(如果有)生成hash值(writeset),保存到hash表中
    【判断这三个事务的writeset是否有冲突,如果没有冲突,则视为同组,如果有冲突,则视为不同组.
    并把把组员编号以及组的编号写进binlog文件中】』
    上面中括号这段要去掉,
    判断writeset之间是否可以并行这个逻辑,是在备库的coordinator线程做的。

    ----
    1. 在多线程并发的时候,Seconds_behind_master很不准,后面会介绍别的判断方法;
    2. 是的,备库有记录,就是show slave status 里面的Relay_Log_File 和 Relay_Log_Pos 这两个值表示的,好问题
    3. ”加入到组是在binlog cache flush到binlog文件之前做的,如果此时有事务正在flush,未sync,则后面的事务必须等待“ 这句话是对的,但是我没看出这个跟前面提的两个延迟参数作用的关系^_^

    2019-01-13
    8
  • 慧鑫coming
    老师,有个问题,mariadb的并行策略,当同一组中有3个事务,它们都对同一行同一字段值进行更改,而它们的commit_id相同,可以在从库并行执行,那么3者的先后顺序是怎么保证不影响该行该字段的最终结果与主库一致?

    作者回复: 好问题
    不过这个是不可能的哈,对同一行的修改,第一个拿到行锁的事务还没提交前,另外两个会被行锁堵住的,这两个进入不了commit状态。所以这三个的commit_id不会相同的😆

    2019-01-11
    7
  • 轻歌赋
    1,3会导致备库仍然单线程执行
    1是因为没有任何事务时间线是一致的
    3是因为单线程执行的事务的先后关系必然不会有重叠的情况,在多线程上面为了保证顺序自然只能一个个过,就成了单线程
    2019-03-12
    3
  • miracle
    请教一个问题,看了三遍还是没明白 文中说的 MariaDB 的并行复制策略 主库上可以一组事务 committing 的同时另一组事务在 running 为什么在从库上就必须要等到 committing 结束之后另一组才能 running呢 希望老师有空能解答下这个问题
    2019-07-20
    2
  • xy🥝
    林老师好,问一个最近遇到的问题。有一台5.7版本的MySQL数据库,在开启多线程复制(4)的时候,跑了两天后,然后三个从库同时卡住了,按照MySQL 1864报错,手动调大了三个从库slave_pending_jobs_size_max的参数之后就恢复了,之前在5.6上没有遇到过这个问题。这里的原理还没想明白,官档上在这里描述的不是很详细,求指导一下。

    作者回复: 主要还是从库的apply线程不够快。。

    2019-04-11
    2
  • linqw
    学习完这篇写下自己的理解,老师有空帮忙看下哦,备库一般会延迟分钟级别,比如主库压力比较大的时候,备库有可能会延迟小时级别,为此mysql官方提供了多种多线程复制策略
    1、5.6基于库的多线程复制策略,使用hash数据库名作为key,value为多少个事务修改此数据库,使用hash来分配多线程,如果一个新事务加入进来,如果有冲突的hash,分配给此线程,如果没有冲突分配给空闲的线程,感觉实现的思路使用队列+线程池,如果线程池中没有空闲的线程,就在队列中增加事务,如果队列满,分发器阻塞,不解析binlog,分发器是生产者,线程池是消费者,基于库的多线程复制有如下优点①构造 hash 值的时候很快,只需要库名;线程的hash项也很少②binlog不需要强制指定row,statement也可以拿到库名。缺点:①如果只有一个库单线程复制,可以将其热点表分布到多个库中(不推荐使用),如果多个库的热点程度不同也会使其单线程复制。
    2、基于表的多线程复制(非官方,老师实现),hash数据库名+表名作为key,value为多少个事务修改此数据表,同一个事务的多张表,在同一个线程进行处理,防止违反原子性,优点对同一个库多个热点表可以同时复制,多表负载效果很好,如果碰到热点表,比如所有的更新事务都会涉及到某一个表的时候,会使用单线程复制。
    3、基于行的多线程复制,key必须是“库名 + 表名 + 唯一键的值“也需考虑唯一主键,防止唯一主键冲突(cpu的多线程调度,顺序不固定),value为修改前后key的次数,约束①表必须有主键②不能有外键③binlog格式row(表复制也一样)缺点:①大事务耗cpu②hash项多。优化可以设置阈值,如果事务修改的行大于特定值,使用单线程复制(老师自己实现)。mysql官网基于行的多线程复制,表示的是对于事务涉及更新的每一行,计算出每一行的 hash保存在writeset中,优点,①是有mysql主库写入binlog中,不需要解析 binlog 内容(event 里的行数据),节省计算量②binlog格式没要求,可以使用statement③无需扫描整个事务的binlog省内存,mysql5.7.22的多线程复制实现方式。
    4、mysql5.7的多线程复制实现方式,借助于处于redo prepare到commit状态下的事务可以并行,因为执行器找引擎拿数据时,事务如果锁冲突会阻塞,无法到写redo log这一步,可以使用binlog故意延迟fsync,防止频繁写磁盘操作,不会丢失数据(redo prepar+完整的binlog事务才能提交,否则回滚),使其在备库多线程复制,主备延迟低,,但是这样有一点不好,语句的响应时间变长,感觉mysql官网故意延迟redo的fsync,在binlog write的时候(因为事务的binlog要写完整,时间较长),使其能批量提交,减少iops,感觉很巧妙

    作者回复: 👍

    2019-03-10
    2
  • J!
    同时处于 prepare 状态的事务,在备库执行时是可以并行.复制的,是这个prepare 就可以生成了改组的commited Id吗

    极客时间版权所有: https://time.geekbang.org/column/article/77083

    作者回复: 进入prepare 的时候就给这个事务分配 commitid,这个commitid就是当前系统最大的一个commitid

    2019-02-01
    2
  • J!
    5.7 版本的基于组提交的并行复制。last_commitid 是在什么时候生成的?

    作者回复: 事务提交的时候

    2019-02-01
    2
  • IceGeek17
    好文,总结对比不同的并行策略,讲的深入浅出,看完豁然开朗。有看源代码的冲动。

    作者回复: 看完分享你的心得哈 👍

    2019-01-24
    2
  • 观弈道人
    丁老师你好,问个题外问题,mysql已经通过gap锁解决了在rr级别下的幻读问题,那么serializable隔离级别目前还有什么用途,一般文章上说的,serializable 主要是为了解决幻读,谢谢回答。

    作者回复: serializable隔离级别确实用得很少(我没有见过在生产上使用的哈)

    2019-01-12
    2
  • MrTrans
    说实话,这一篇文章我在按表复制和按行复制这一段还是清晰的,但是到了并行复制就不是很清晰了。在MySQL5.7.22新增了binlog-transaction-dependency-tracking,用来控制是都使用并行复制策略,分为commit_order,writeset和writeset_session。如果需要备库赶快追上主库,那么就需要更快的并行复制策略,在这里我选择设置为writeset,为什么,因为writeset不需要解析binlog的内容,直接并发执行处理冲突,而commit_order需要在prepare阶段和commit阶段判断是否可以并行,这样会退化成单线程,再并行复制,writeset_session还要在writeset的基础上多一个约束,要保证先后顺序,保证过先后顺序就会退化成单线程!
    2019-08-30
    1
  • godtrue
    原理基本能明白,不过细节基本都在刷新自己的认知,万幸遇到如此牛逼的老师。
    一个生产的快一个消费的慢,延迟何止几个小时,恐怕此生都赶不上。
    2019-08-03
    1
  • null
    原文:
    这时候主库对表 t 做了一个加字段操作,即使这个表很小,这个 DDL 在备库应用的时候也会被堵住,也〖不能〗看到这个现象。

    老师,应该是也能看到这个现象吧
    2019-07-04
    1
  • 唯她命
    老师 我觉得图6好像有问题啊,在图5中,主库trx1,trx2,trx3是同一组里面的事务,他们拥有相同的commit_id,他们到备库里面需要被分到不同的worker里面去执行,但是图6里面,trx1,trx2,trx3还依然在一组里面?这是不是矛盾了?

    作者回复: 这里没有矛盾哈。图6中,画在同一组的,就表示可以并行执行。也就是说,图6中,123是并行执行,然后456并行,然后789并行

    2019-04-08
    1
    1
  • 胡楚坚
    老师,关于留言板中置顶留言长杰的问题:一个事务更新了两张表的数据,然后两个更新语句分给了两个worker。这问题我有点不明白,因为看完专栏我的认知是一个事务只会给一个worker执行,这样就不会有先后commit问题。请问老师是我看漏了什么吗?这种情况应该会出现在哪种策略?

    作者回复: 一个事务只能发给一个worker的,

    长杰评论的那个问题,讨论的是如果分成两个事务,然后约定一起提交,这个是做不到的(或者说实现起来很复杂)

    2019-02-18
    1
收起评论
61
返回
顶部