专栏首页洁癖是一只狗Mysql为什么会抖一下呢

Mysql为什么会抖一下呢

在之前我们说过酒店记账的故事,其中酒店掌柜记账的的黑板就类似我们的redo log,而掌柜的记账本就是数据文件,掌柜的记忆就是内存。

而掌柜的要把黑板的数据记录在账本上,就类似我们把内存的数据写入磁盘的过程,这个过程就叫做flush.在flush之前,掌柜的账本和掌柜的记忆其实是不一致的,因为有部分数据还在黑板上,

往往当内存的数据和磁盘的数据不一致的时候,我们称这个内存也就是脏页,内存数据写入磁盘后,数据保持一致,我们称之为干净页。

在日常工作中,我们执行sql的非常快,那是因为,我们仅仅在写内存和日志,但是偶尔会发数据库抖了一下,sql执行很慢,这个时候可能就是在刷脏页.

那什么时候会发生flush呢

  1. 黑板满了,记不下了,这个时候掌柜的就会放下手上的事,去把黑板的数据记录擦掉,流出空位记录新的记录,这个场景就是我们的redo log记录满了,这个时候系统就会停止所有的更新操作,把checkpoint向前推进,留出空间可以继续写.
  2. 这天生意太好了,要记录的数据太多,掌柜的发现快记不住了,就必须先把部分数据记录在账本上,这种场景就是我们的内存不够了,就要淘汰一些数据页,空出内存给别的数据页使用,如果淘汰的是脏页,就必须先写入磁盘,有人会问为什么不去内存淘汰掉呢,下次需要的时候,从磁盘读取,在把redo log拿出来应用不就行了吗,这里是考虑性能问题,如果刷脏页就一定会写磁盘,就保证了数据页的两种状态.
    1. 一种是数据在内存中,内存里肯定是正确的数据,直接返回
    2. 一种内存中没有数据,可以肯定数据文件是正确的结果,读入内存后返回,这样的效率最高
  3. 生意不忙的时候,掌柜的闲着没有事做,就可以把黑板的数据,写入账本,正如我们的mysql系统空闲的时候,mysql只要有时间就会刷一下脏页.
  4. 还有就是酒店关门了,休业了,我们这个时候也可以把账目结一下,对应的就是mysql关闭,这个时候mysql就可以把内存的脏页都flush到磁盘上,下次启动的时候,直接从磁盘上读取数据

上面的四种场景,3,4两种场景对性能没有影响,主要分析一下1,2两种场景。

第一种redolog写满了,要刷新脏页,这个情况系统要尽量避免,因为这样会导致系统无法接受更新,

第二种情况,内存不够用了,就会把脏页写到磁盘,这种其实是常态,innoDB管理内存,缓冲中的内存也有三种状态

  • 还没有使用
  • 使用了并且是干净页
  • 使用了并且是脏页

innoDB要尽量使用内存,因此对于一个长时间运行的库来说,未被使用的页面很少.而当内存不够的时候,就要向缓冲池申请数据页,这个时候只能把最久不使用的数据页从内存中淘汰掉,如果是干净页,就直接释放复用,如果是脏页,就必须先刷新到磁盘,变成干净页之后复用。

尽管刷脏页的是常态但是出现下面两种情况,都会影响性能

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

所以innodb需要有控制脏页比例机制,来尽量避免上面的两种情况,

Innodb刷脏页的控制策略

首先,你要正确的告诉innoDB所在主机的IO能力,这样innodb才能知道需要全力刷脏页的速度,能有多快。我们可以设置innodb_io_capacity这个参数,他会告诉你磁盘的能力,这个值建议你设置成磁盘的IOPS,磁盘的IOPS可以通过下面工具测试,如下面语句

 fio -filename=$filename -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest 

如果不能正确的设置这个参数,导致的性能问题也是比比皆是.

虽然我们说的是全力刷脏页,但是实际上我们并不能这样去做,后台还有其他的请求,因此我们要看看如何控制引擎按照全力的百分比刷脏页

控制刷脏页的速度有哪些因素呢

刷盘的速度一般有两方面,一个是脏页比例,一个是redo log写盘速度,

  1. 脏页的比例上限默认是75%,我们可以假设比例是M我们可以根据这个M计算出一个0-100的一个值,
  2. 而innoDB每次写入的日志都有一个序号,当前写入的序号跟checkpoint对应的序号之间的差值,我们假设这个值为N
  3. 然后根据F(N)和F(M)两个中较大的值为R,之后引擎就可以按照innodb_io_capacity定义的能力乘以R%开控制刷脏页的速度。

现在我们知道,innodb会在后台刷脏页,而刷脏页的过程无论将内存写入磁盘,还是刷脏页占用IO的资源并可能影响到了你的更新语句,都有可能让业务员感到mysq抖一下的原因。

我们要避免避免inndb_io_capacity的值,并在平常要多关心脏页比例,不要让他接近75%。脏页的比例=innodb_buffer_poll_pages_dirty/innodb_buffer_pool_total,具体命令如下

mysql> 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;

一旦一个查询请求需要执行的过程需要刷脏页的时候,就可能非常慢,而mysql还有另外一个机制,当一个脏页要被刷到磁盘的时候,发现旁边的数据页也是脏页,也会把这个脏页一块刷到磁盘,这种机制可能会导致,很多脏页一起被刷新到磁盘,从而影响性能,对应的控制参数是innodb_flush_neighbors就是控制这个机制的,0是关闭,只会自己刷自己,1打开,会导致上面那样影响性能,

但是对于机械硬盘这样做是很有意义的,可以减少很多随机io,机械硬盘的随机IOPS一般只有几百,相同的逻辑操作减少io磁盘,提高系统性能,

而如果是SSD这类IOPS比较高的设备的话,我们一般建议把innodb_flush_neighbors设置成0.仅仅刷自己,进而更快的刷脏页的操作,减少sql语句响应时间,而mysql8.0中就已经把0设置成了默认值.

欢迎持续关注,如果对你有一丝丝帮助,麻烦点个关注,也欢迎点赞转发

本文分享自微信公众号 - 洁癖是一只狗(rookie-dog),作者:洁癖汪

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2020-09-07

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 如何解决高并发I/O瓶颈

    在现在这个大数据时代下,IO的性能问题更是尤为突出,IO读写已经成为应用场景的瓶颈,不容我们忽视,今天,我们就深入了解下Java IO在高并发,大数据场景下暴露...

    小土豆Yuki
  • Mysql基础篇--面试锁机制

    数据库锁就是一种保证数据一致性而使各种共享资源在被并发访问,并发访问人有序所设计的一种规则。

    小土豆Yuki
  • Nginx基础篇--docker搭建nginx

    Nginx是一个轻量级,高性能,基于HTTP的,反向代理服务器,同时还是一个电子邮件服务器,Nginx是有俄罗斯人用c语言开发的,2004年10月发布第一个版本...

    小土豆Yuki
  • SQL中JOIN时条件放在Where和On的区别

    SQL中JOIN子句是用于把来自两个或多个表的数据连接起来,在这个过程中可能会添加一些过滤条件。昨天有小伙伴问,如下图的这两种SQL写法查询结果是否会一样?(好...

    用户2769421
  • linux终端输出彩虹色文本

    Lolcat是用于Linux,BSD和OSX的实用程序,其连接方式类似于cat命令,并为其添加了彩虹色。Lolcat主要用于Linux Terminal中的文本...

    小柒吃地瓜
  • NOIP训练营集训笔记—信息学基础算法(倍增与分治算法)

    本文摘自清北OI学堂内部笔记,作者潘恺璠,来自柳铁一中曾参加过清北训练营提高组精英班,主要记录的是信息学基础算法。笔记非常详细,特分享给大家! NOIP2019...

    用户5325900
  • 利用C#迭代器的一个杨辉三角示例

    身边有个朋友在跟着廖雪峰的教程学习python,途中遇到了“在Python中使用迭代器打印杨辉三角”的问题,我在帮忙解决的同时顺手写了个简单的C#版本以供补充。...

    潘成涛
  • Newbe.Claptrap-一套以“事件溯源”和“Actor模式”作为基本理论的服务端开发框架

    本文是关于 Newbe.Claptrap 项目主体内容的介绍,读者可以通过这篇文章,大体了解项目内容。

    newbe36524
  • Day2平衡树笔记

    线段树不支持的操作:删除,插入 ---- 常见的平衡树 treap 慢||好写 sbt(大小平衡的树) 非常快 比较好写 ||功能不全 rbt 红黑树 特...

    attack
  • 传统网络设备商如何向VNF厂商成功转型

    SDNLAB

扫码关注云+社区

领取腾讯云代金券