开发者社区> 欢少的成长之路> 正文

面试高频:为什么MySQL会抖一下?

简介: 大家好前面我们大概了解了如何巧妙的给字符串字段加索引提高查询性能。今天我们介绍一下为什么MySQL在查询数据的时候,有些时候会 "抖" 一下
+关注继续查看

先解释一下抖这个字。有些时候的SQL执行非常快,有些时候执行非常慢。通过explain查看SQL的执行计划还是无用。该走的索引也走了,该优化的细节也优了。那么到底是因为什么所以才导致卡顿一下的呢?

首先介绍一下什么是干净页跟脏页。先把路铺好。


干净页

内存上的数据和磁盘上的数据页的内容一致时,称为 “干净页”。

脏页

内存上的数据和磁盘上的数据页的内容不一致时,称为 “脏页”。

举个例子

举一个干净页跟脏页的例子,这个例子也是前几篇文章举的。一家农村型超市,这个超市没有大城市那种不赊账的规矩。所以农村的超市一般会涉及到赊账。那么如何进行有效的记账呢?我们不可能因为赊了一个人就记一次,那这样的话做事的效率是非常低的。因为每次在记账本上找到那个人的名字都要找很久。所以一般采用的是,会有一个临时纸条,这个纸条夹在一个固定的地方,每当晚上打样的时候,统一把所有纸条上的数据一一归到记账本上。

这样才是大多数超市的真实场景,那么数据库里这些是如何实现的呢?纸条就是redo log!记账本就是磁盘!

如下图所示

  1. 第一个小方格中的内容:当前赊账的数据还没有转移到记账本上。所以内存与磁盘上的数据不一致。这样就是一开始介绍的脏页。
  2. 第二个小方格中的内容:到了晚上或者不忙的时候,掌柜或者老板就把纸条上的数据转移到记账本上。这个过程在数据库中就是flush数据的过程。

综上所述:干净页,脏页大概就是这么一个东西。数据库之所以抖一下,就是因为数据库的内部正在进行flush操作

image.png

图1 超市赊账纸条转移到记账本的过程

那么数据库什么时候才会抖一下呢?什么时候开始flush呢?今天我们详细介绍一下


内存场景

我们介绍场景的时候,还是按照上述例子,先从简单易懂的真实场景,再到数据库抽象的过度!下面四个场景看不懂没关系,我们一一介绍。这篇文章还是比较偏底层介绍的,所以耐心一点,看完之后再回味一下。


纸条满了

第一种场景就是,所有纸条都记满了,这个时候必须放下手里的工作。把纸条上的所有账都转移到记账本上。对应的数据库过程就是。redo log满了。需要把redo log日志上的操作转移到磁盘中。变成真正的数据。


一张纸条满了

第二种场景就是,一张纸条满了,掌柜的不得不再找一张纸条过来,继续记账。对应的数据库流程就是,一页数据写满了,MySQL不得不再开一页进行存放数据。


纸条没满,不忙的时候

第三种场景就是,在超市低峰期的时候,都是比较悠闲的。所以这个时候老板闲着也是闲着,不如把纸条上的账,归到记账本上。对应的数据库流程就是,一段时间没人使用数据库的时候,数据库就会自己把redo log日志更新到磁盘上。


打样时

第四种场景就是,超市晚上打样时,完全没人。掌柜的收尾一天工作的时候把账归到记账本上。对应的数据库流程就是,用户关闭会话的时候。没有连接使用了。数据库也会把redo log更新到磁盘上。


分析

第三种情况与第四种情况都是属于空闲时,系统没什么压力。我们就不做分析了。这里着重分析一下第一种与第二钟情况。

纸条满了:“redo log 写满了,要 flush 脏页”,这种情况是 InnoDB 要尽量避免的。因为出现这种情况的时候,整个系统就不能再接受更新了,所有的更新都必须堵住。如果你从监控上看,这时候更新数会跌为 0。

一张纸条满了:“内存不够用了,要先将脏页写到磁盘”,这种情况其实是常态。InnoDB 用缓冲池(buffer pool)管理内存,缓冲池中的内存页有三种状态:还没有使用的,使用了并且是干净页,使用了并且是脏页。

innodb的策略是尽量使用内存,提高自身性能,因此对于一个长时间运行的库来说,未被使用的页面很少。

而当要读入的数据页没有在内存的时候,就必须到缓冲池中申请一个数据页。这时候只能把最久不使用的数据页从内存中淘汰掉:如果要淘汰的是一个干净页,就直接释放出来复用;但如果是脏页呢,就必须将脏页先刷到磁盘,变成干净页后才能复用。

所以刷脏页是数据库运行的常态了。那么下面两种情况会影响数据库的运行。也就是一开始我们说的数据库为什么会抖音一下的详细原因:

  1. 一个查询要淘汰的脏页个数太多,会导致查询的响应时间明显变长;
  2. 日志写满,更新全部堵住,写性能跌为 0,这种情况对敏感业务来说,是不能接受的。

所以MySQL想用的稳定,用的爽就必须控制,刷脏页的时机。以及何时刷脏页,刷多块?


innodb刷脏页策略

这里解决一下何时刷脏页,刷多块的问题

首先,你要正确地告诉 InnoDB 所在主机的 IO 能力,这样 InnoDB 才能知道需要全力刷脏页的时候,可以刷多快。下列是数据库用到的参数,通过他来设置。怎么设置我们下面会介绍的。

show variables like 'innodb_io_capacity'

image.png

innodb_io_capacity 不能过高,也不能过低。过高的话会占用大量内存,会严重影响MySQL的日常使用。如果过低会导致脏页太多。数据跟不上使用。导致写入只能等。


如何设置

首先主要考虑两个因素。一个是脏页比例,一个是redo log写盘速度。

下列参数是控制脏页比例。默认值为75。也就是75%

show variables like 'innodb_max_dirty_pages_pct'

InnoDB 会根据当前的脏页比例(假设为 M),算出一个范围在 0 到 100 之间的数字

InnoDB 每次写入的日志都有一个序号,当前写入的序号跟 checkpoint 对应的序号之间的差值,我们假设为 N。InnoDB 会根据这个 N 算出一个范围在 0 到 100 之间的数字,这个计算公式可以记为 F2(N)。F2(N) 算法比较复杂,你只要知道 N 越大,算出来的值越大就好了。

然后,根据上述算得的 F1(M) 和 F2(N) 两个值,取其中较大的值记为 R,之后引擎就可以按照 innodb_io_capacity 定义的能力乘以 R% 来控制刷脏页的速度。

如下图,图中的 F1、F2 就是上面我们通过脏页比例和 redo log 写入速度算出来的两个值。

image.png

现在你知道了,InnoDB 会在后台刷脏页,而刷脏页的过程是要将内存页写入磁盘。所以,无论是你的查询语句在需要内存的时候可能要求淘汰一个脏页,还是由于刷脏页的逻辑会占用 IO 资源并可能影响到了你的更新语句,都可能是造成你从业务端感知到 MySQL“抖”了一下的原因。

要尽量避免这种情况,你就要合理地设置 innodb_io_capacity 的值,并且平时要多关注脏页比例,不要让它经常接近 75%。

其中,脏页比例是通过 Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total 得到的,具体的命令参考下面的代码:

select VARIABLE_VALUE into @a 
from global_status 
where VARIABLE_NAME = 'Innodb_buffer_pool_pages_dirty';
select VARIABLE_VALUE into @b 
from global_status 
where VARIABLE_NAME = 'Innodb_buffer_pool_pages_total';
select @a/@b;

下面科普一个有趣的策略。一旦一个查询请求需要在执行过程中先 flush 掉一个脏页时,这个查询就可能要比平时慢了。而 MySQL 中的一个机制,可能让你的查询会更慢:在准备刷一个脏页的时候,如果这个数据页旁边的数据页刚好是脏页,就会把这个“邻居”也带着一起刷掉;而且这个把“邻居”拖下水的逻辑还可以继续蔓延,也就是对于每个邻居数据页,如果跟它相邻的数据页也还是脏页的话,也会被放到一起刷。

紧接着相关联的参数是set innodb_flush_neighbors=1 允许连坐机制set innodb_flush_neighbors=0 不允许连坐机制

这个策略还是比较好的。这个优化在机械硬盘时代是很有意义的,可以减少很多随机 IO。机械硬盘的随机 IOPS 一般只有几百,相同的逻辑操作减少随机 IO 就意味着系统性能的大幅度提升。

而如果使用的是 SSD 这类 IOPS 比较高的设备的话,我就建议你把 innodb_flush_neighbors 的值设置成 0。因为这时候 IOPS 往往不是瓶颈,而“只刷自己”,就能更快地执行完必要的刷脏页操作,减少 SQL 语句响应时间。

在 MySQL 8.0 中,innodb_flush_neighbors 参数的默认值已经是 0 了。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
如何设置阿里云服务器安全组?阿里云安全组规则详细解说
阿里云安全组设置详细图文教程(收藏起来) 阿里云服务器安全组设置规则分享,阿里云服务器安全组如何放行端口设置教程。阿里云会要求客户设置安全组,如果不设置,阿里云会指定默认的安全组。那么,这个安全组是什么呢?顾名思义,就是为了服务器安全设置的。安全组其实就是一个虚拟的防火墙,可以让用户从端口、IP的维度来筛选对应服务器的访问者,从而形成一个云上的安全域。
18581 0
阿里云服务器如何登录?阿里云服务器的三种登录方法
购买阿里云ECS云服务器后如何登录?场景不同,阿里云优惠总结大概有三种登录方式: 登录到ECS云服务器控制台 在ECS云服务器控制台用户可以更改密码、更换系.
27727 0
阿里云服务器安全组设置内网互通的方法
虽然0.0.0.0/0使用非常方便,但是发现很多同学使用它来做内网互通,这是有安全风险的,实例有可能会在经典网络被内网IP访问到。下面介绍一下四种安全的内网互联设置方法。 购买前请先:领取阿里云幸运券,有很多优惠,可到下文中领取。
21933 0
阿里云服务器端口号设置
阿里云服务器初级使用者可能面临的问题之一. 使用tomcat或者其他服务器软件设置端口号后,比如 一些不是默认的, mysql的 3306, mssql的1433,有时候打不开网页, 原因是没有在ecs安全组去设置这个端口号. 解决: 点击ecs下网络和安全下的安全组 在弹出的安全组中,如果没有就新建安全组,然后点击配置规则 最后如上图点击添加...或快速创建.   have fun!  将编程看作是一门艺术,而不单单是个技术。
19980 0
阿里云服务器ECS登录用户名是什么?系统不同默认账号也不同
阿里云服务器Windows系统默认用户名administrator,Linux镜像服务器用户名root
15291 0
腾讯云服务器 设置ngxin + fastdfs +tomcat 开机自启动
在tomcat中新建一个可以启动的 .sh 脚本文件 /usr/local/tomcat7/bin/ export JAVA_HOME=/usr/local/java/jdk7 export PATH=$JAVA_HOME/bin/:$PATH export CLASSPATH=.
14852 0
使用OpenApi弹性释放和设置云服务器ECS释放
云服务器ECS的一个重要特性就是按需创建资源。您可以在业务高峰期按需弹性的自定义规则进行资源创建,在完成业务计算的时候释放资源。本篇将提供几个Tips帮助您更加容易和自动化的完成云服务器的释放和弹性设置。
20879 0
+关注
欢少的成长之路
有物流,电商经验,RocketMQ领域专家,csdn/掘金等平台优质作者,就职于物流企业Java开发岗位
98
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
JS零基础入门教程(上册)
立即下载
性能优化方法论
立即下载
手把手学习日志服务SLS,云启实验室实战指南
立即下载