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讲
登录|注册

12 | 为什么我的MySQL会“抖”一下?

林晓斌 2018-12-10
平时的工作中,不知道你有没有遇到过这样的场景,一条 SQL 语句,正常执行的时候特别快,但是有时也不知道怎么回事,它就会变得特别慢,并且这样的场景很难复现,它不只随机,而且持续时间还很短。
看上去,这就像是数据库“抖”了一下。今天,我们就一起来看一看这是什么原因。

你的 SQL 语句为什么变“慢”了

在前面第 2 篇文章《日志系统:一条 SQL 更新语句是如何执行的?》中,我为你介绍了 WAL 机制。现在你知道了,InnoDB 在处理更新语句的时候,只做了写日志这一个磁盘操作。这个日志叫作 redo log(重做日志),也就是《孔乙己》里咸亨酒店掌柜用来记账的粉板,在更新内存写完 redo log 后,就返回给客户端,本次更新成功。
做下类比的话,掌柜记账的账本是数据文件,记账用的粉板是日志文件(redo log),掌柜的记忆就是内存。
掌柜总要找时间把账本更新一下,这对应的就是把内存里的数据写入磁盘的过程,术语就是 flush。在这个 flush 操作执行之前,孔乙己的赊账总额,其实跟掌柜手中账本里面的记录是不一致的。因为孔乙己今天的赊账金额还只在粉板上,而账本里的记录是老的,还没把今天的赊账算进去。
当内存数据页跟磁盘数据页内容不一致的时候,我们称这个内存页为“脏页”。内存数据写入到磁盘后,内存和磁盘上的数据页的内容就一致了,称为“干净页”
取消
完成
0/1000字
划线
笔记
复制
© 版权归极客邦科技所有,未经许可不得传播售卖。 页面已增加防盗追踪,如有侵权极客邦将依法追究其法律责任。
该试读文章来自付费专栏《MySQL实战45讲》,如需阅读全部文章,
请订阅文章所属专栏。
立即订阅
登录 后留言

精选留言(115)

  • Tony Du 置顶
    当内存不够用了,要将脏页写到磁盘,会有一个数据页淘汰机制(最久不使用),假设淘汰的是脏页,则此时脏页所对应的redo log的位置是随机的,当有多个不同的脏页需要刷,则对应的redo log可能在不同的位置,这样就需要把redo log的多个不同位置刷掉,这样对于redo log的处理不是就会很麻烦吗?(合并间隙,移动位置?)
    另外,redo log的优势在于将磁盘随机写转换成了顺序写,如果需要将redo log的不同部分刷掉(刷脏页),不是就在redo log里随机读写了么?

    作者回复: 好问题。

    其实由于淘汰的时候,刷脏页过程不用动redo log文件的。

    这个有个额外的保证,是redo log在“重放”的时候,如果一个数据页已经是刷过的,会识别出来并跳过。

    2018-12-10
    5
    97
  • 某、人 置顶
    redo log是关系型数据库的核心啊,保证了ACID里的D。所以redo log是牵一发而动全身的操作
    按照老师说的当内存数据页跟磁盘数据页不一致的时候,把内存页称为'脏页'。如果redo log
    设置得太小,redo log写满.那么会涉及到哪些操作呢,我认为是以下几点:
    1.把相对应的数据页中的脏页持久化到磁盘,checkpoint往前推
    2.由于redo log还记录了undo的变化,undo log buffer也要持久化进undo log
    3.当innodb_flush_log_at_trx_commit设置为非1,还要把内存里的redo log持久化到磁盘上
    4.redo log还记录了change buffer的改变,那么还要把change buffer purge到idb
    以及merge change buffer.merge生成的数据页也是脏页,也要持久化到磁盘
    上述4种操作,都是占用系统I/O,影响DML,如果操作频繁,会导致'抖'得向现在我们过冬一样。
    但是对于select操作来说,查询时间相对会更快。因为系统脏页变少了,不用去淘汰脏页,直接复用
    干净页即可。还有就是对于宕机恢复,速度也更快,因为checkpoint很接近LSN,恢复的数据页相对较少
    所以要控制刷脏的频率,频率快了,影响DML I/O,频率慢了,会导致读操作耗时长。
    我是这样想的这个问题,有可能不太对,特别是对于第4点是否会merge以及purge,还需要老师的解答

    作者回复: 抖得像过冬一样,😄👍🏿

    你说得很对,第4点没错的,出现这种情况的时候,连change buffer的优化也没意义了

    2018-12-10
    56
  • yesir 置顶
    我观察了下公司的数据库确实发现了抖动现象,有几个问题,
    1)Innodb_buffer_pool_pages_total这个值很大,百万级别的,而且数值不像是人为设置上去的,是怎么来的呢?
    2)Innodb_buffer_pool_pages_dirty达到4万多的时候就开始flush了,脏页比例是75,这肯定是远达不到的,ssd磁盘,innodb_io_capacity是200,肯定可以提高。文章中说flush的触发条件有2个,一个是内存不够了,一个是redo log 满了,那么我这个场景是哪种情况呢

    作者回复: 1) 这个是innodb 数据页总是,过百万是正常的,16K一个,Bufree pool size 16G 就是100万了

    2)你这个例子就是io_capacity设太小了…

    2018-12-11
    2
    14
  • Ryoma 置顶
    关于粉板和redo log的类比我觉得有一点不太合适:redo log记录的是实时欠款,比如账本中是10文,又欠了9文,此时redo log 记录的是19;而粉板的话,只会追加某人欠款+9文,不会关注原来已欠款多少(不然某人赊账时,我还需要找到账本中的这个人,才知道他之前欠款多少,我觉得这个场景跟MySQL中的场景还是有区别的)

    作者回复: Redo log里也是记的+9哦😄

    2018-12-12
    3
    9
  • melon
    又思考了一下,请老师帮忙看一下理解的对不对:buffer pool里维护着一个脏页列表,假设现在redo log 的 checkpoint 记录的 LSN 为 10,现在内存中的一干净页有修改,修改后该页的LSN为12,大于 checkpoint 的LSN,则在写redo log的同时该页也会被标记为脏页记录到脏页列表中,现在内存不足,该页需要被淘汰掉,该页会被刷到磁盘,磁盘中该页的LSN为12,该页也从脏页列表中移除,现在redo log 需要往前推进checkpoint,到LSN为12的这条log时,发现内存中的脏页列表里没有该页,且磁盘上该页的LSN也已经为12,则该页已刷脏,已为干净页,跳过。

    作者回复: 对的。👍🏿

    2018-12-11
    35
  • jimmy
    老师,我想问一下,innodb是如何知道一个页是不是脏页的,是有标记位还是通过redolog的ckeckpoint来确定的?

    作者回复: 每个数据页头部有LSN,8字节,每次修改都会变大。

    对比这个LSN跟checkpoint 的LSN,比checkpoint小的一定是干净页

    2018-12-10
    27
  • 岁月安然
    “内存不够用了,要先将脏页写到磁盘“和“redo log 写满了,要 flush 脏页”可以理解为一个脏页本身占用内存,释放内存需要将脏页写入到磁盘才能释放。而redo log写满只有当redo log对应的脏页flush到磁盘上才能释放对应空间。有几个问题:
    1、“内存不够用了,要先将脏页写到磁盘“redo log对应的空间会释放嘛?“redo log 写满了,要 flush 脏页”对应的内存页会释放嘛?
    2、将脏页flush到磁盘上是直接将脏页数据覆盖到对应磁盘上的数据?还是从磁盘上取到数据后取根据redo log记录进行更新后再写入到磁盘?
    3、redo log是怎么记录对应脏页是否已经flush了?如果断电了重启导致内存丢失,前面几章说通过redo log进行数据恢复那redo log又怎么去释放空间?

    作者回复: 1. Redolog 的空间是循环使用的,无所谓释放。 对应的内存页会变成干净页。但是等淘汰的时候才会逐出内存

    2. 好问题,前者

    3. 不用记,重启了就从checkpoint 的位置往后扫。 如果已经之前刷过盘的, 不会重复应用redi log。 好问题

    2018-12-10
    25
  • godtrue
    1:MySQL抖一下是什么意思?

    抖我认为就是不稳定的意思,一个SQL语句平时速度都挺快的,偶尔会慢一下且没啥规律,就是抖啦!

    2:MySQL为啥会抖一下?

    因为运行的不正常了,或者不稳定了,需要花费更多的资源处理别的事情,会使SQL语句的执行效率明显变慢。针对innoDB导致MySQL抖的原因,主要是InnoDB 会在后台刷脏页,而刷脏页的过程是要将内存页写入磁盘。所以,无论是你的查询语句在需要内存的时候可能要求淘汰一个脏页,还是由于刷脏页的逻辑会占用 IO 资源并可能影响到了你的更新语句,都可能是造成你从业务端感知MySQL“抖”了一下的原因。

    3:MySQL抖一下有啥问题?

    很明显系统不稳定,性能突然下降对业务端是很不友好的。

    4:怎么让MySQL不抖?

    设置合理参数配配置,尤其是设置 好innodb_io_capacity 的值,并且平时要多关注脏页比例,不要让它经常接近 75%

    5:啥是脏页?

    当内存数据页跟磁盘数据页内容不一致的时候,我们称这个内存页为“脏页”。

    按照这个定义感觉脏页是不可避免的,写的时候总会先写内存再写磁盘和有没有用WAL没啥关系?

    6:啥是干净页?

    内存数据写入到磁盘后,内存和磁盘上的数据页的内容就一致了,称为“干净页”。

    7:脏页是咋产生的?

    因为使用了WAL技术,这个技术会把数据库的随机写转化为顺序写,但副作用就是会产生脏页。

    8:啥是随机写?为啥那么耗性能?

    随机写我的理解是,这次写磁盘的那个扇区和上一次没啥关系,需要重新定位位置,机械运动是很慢的即使不是机械运动重新定位写磁盘的位置也是很耗时的。

    9:啥是顺序写?

    顺序写我的理解是,这次写磁盘那个扇区就在上一次的下一个位置,不需要重新定位写磁盘的位置速度当然会快一些。

    10:WAL怎么把随机写转化为顺序写的?

    写redolog是顺序写的,先写redolog等合适的时候再写磁盘,间接的将随机写变成了顺序写,性能确实会提高不少。
    2019-06-30
    1
    13
  • 老鱼头
    那个说fio命令会破坏硬盘的兄弟,是没用对命令。估计把-filename=/dev/sdb1 。。。这个的意思是从 分区 sdb1 的第一个扇区开始写入随机数据,去判断这个磁盘的写入速度。如果指定路径+文件名就不会出这事了~比如老师给的例子~

    作者回复: 嗯嗯,你说的对

    写文章的时候,我还故意用变量,这样直接拷贝会出错,然后自己再写个路径,已经考虑了安全了😓

    2018-12-12
    1
    9
  • WL
    把该讲内容总结为几个问题, 大家复习的时候可以先尝试回答这些问题检查自己的掌握程度:

    1.
     脏页和干净页的定义是什么?
    2.
    引发数据库flush脏页的四种典型场景各是什么?对mysql性能的影响各是怎样的?
    3.
    缓存池中的内存页有哪三种状态? 哪两种刷脏页的情况会比较影响性能?
    4.
    innodb_io_capacity这个参数的作用是什么,这个参数设置错误可能导致什么样的后果, 如何正确的设置这个参数?
    5.
    mysql如何通过innodb_io_capacity, 脏页比例(M), 当前日志序号(N)这三个指标来控制以什么样的速度去刷脏页的?
    6.
    innodb_flush_neighbor这个参数表示什么意思,应该如何设置?

    2018-12-16
    8
  • Tony Du
    当内存不够用了,要将脏页写到磁盘,会有一个数据页淘汰机制(最久不使用),假设淘汰的是脏页,则此时脏页所对应的redo log的位置是随机的,当有多个不同的脏页需要刷,则对应的redo log可能在不同的位置,这样就需要把redo log的多个不同位置刷掉,这样对于redo log的处理不是就会很麻烦吗?(合并间隙,移动位置?)
    另外,redo log的优势在于将磁盘随机写转换成了顺序写,如果需要将redo log的不同部分刷掉(刷脏页),不是就在redo log里随机读写了么?

    作者回复
    好问题。

    其实由于淘汰的时候,刷脏页过程不用动redo log文件的。

    这个有个额外的保证,是redo log在“重放”的时候,如果一个数据页已经是刷过的,会识别出来并跳过。

    我的回复
    这个额外保证是如何做到的?能不能稍微解释下
    通过刷脏页时数据页更新的timestamp来对比redo log的timestamp?

    作者回复: LSN,每次写redo log都带的一个数字, 数据页上也有,对比大小的,因为太细节没有写到文章中。

    2018-12-11
    7
  • lionetes
    很多测试人员再做压力测试的时候 出现刚开始 insert update 很快 一会 就出现很慢,并且延迟很大,大部分是因为redo log 设置太小 引起的,完美诠释

    作者回复: 👍🏿 常见的误用场景

    2018-12-11
    6
  • 包包up
    redo-log内存满了,不停的要刷脏页回磁盘。现象就会是发现机器io不高,但是mysql明显的卡顿。
    2018-12-10
    6
  • 喔~
    老师,请问下访问某条记录时,存储引擎是如何判断这条记录所在的数据页是否在内存当中,这个查内存机制是如何实现的?

    作者回复: 每个页面有编号的。拿着编号去内存看,没有,就去磁盘

    2018-12-20
    5
  • 董航
    怎么说呢,越看越明白,哈哈,前面不懂的,后面就懂了

    作者回复: 👍🏿

    你领会到实践篇的“奥义”了😄

    一边引入新知识点,一边应用前面的

    2018-12-10
    5
  • Ying
    redolog 设置过小,会导致频繁刷脏页,还可能引发连坐,这样抖的频率可能会明显变高,系统会不断卡死的
    2018-12-10
    5
  • greatcl
    二刷中。
    我觉得Ryoma 的疑问可能是因为《图 1 “孔乙己赊账”更新和 flush 过程》中,日志/粉板的内容写成了“10改成19”,其实应该是“+9”。而“内存/掌柜记忆”的内容是否是19要根据原有内容是否在内存中来区别。如果掌柜记忆够好或者孔乙己赊账前是否先问了下:我现在欠多少钱?老板查了下回答:10文,那么此时内存的值是会被直接改为19,否则内存中只是change buffer的记录+9。
    不知这样理解是否正确
    2019-04-24
    3
  • Geek_477c02
    InnoDB 认为这个系统的能力就这么差,所以刷脏页刷得特别慢,甚至比脏页生成的速度还慢,这样就造成了脏页累积,影响了查询和更新性能。为什么会影响性能,是因为要读取的数据不在内存中,但是脏页过多导致新页必须等待脏页刷盘导致的么?

    作者回复: 理解准确👍

    2019-01-29
    3
  • redo日志设置太小,内存又比较大,导致innodb缓存的脏页还没多少就开始大量flush,刷写频率增大。感觉有点像jvm中,年轻代内存设置小点,导致频繁younggc。当然这就是个权衡,毕竟redo和内存不能无限大。

    作者回复: 👍🏿引申不错

    2018-12-10
    3
  • Lucus
    思考题:
    redo log设置的太小,应该是很容易引发redo log满了要flush,这时候所有的更新都会阻塞。内存的优势没有利用起来,如果是更新频繁的库会导致整个库都不可用。
    p.s. 林老师能不能详细讲一下innodb_io_capacity这个参数与系统的配置关系,如何根据系统调整最佳值?
    2019-04-01
    2
收起评论
99+
返回
顶部