欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

MySQL 笔记整理(12) --为什么我的MySQL会“抖”一下?

程序员文章站 2022-06-04 20:45:05
笔记记录自林晓斌(丁奇)老师的《MySQL实战45讲》 (本篇内图片均来自丁奇老师的讲解,如有侵权,请联系我删除) 12) --为什么我的MySQL会“抖”一下? 断更了一段时间,因为这几周实在是太忙了,周末加班两天那种。。。 有时你会遇到这样的问题,一条SQL语句,正常执行的时候很快,但是有时候会 ......

笔记记录自林晓斌(丁奇)老师的《mysql实战45讲》

(本篇内图片均来自丁奇老师的讲解,如有侵权,请联系我删除)

12) --为什么我的mysql会“抖”一下?

  断更了一段时间,因为这几周实在是太忙了,周末加班两天那种。。。

  有时你会遇到这样的问题,一条sql语句,正常执行的时候很快,但是有时候会变得特别慢。并且这种场景很难复现,不只随机而且持续时间很短。这其实与mysql的脏页以及它的刷新机制有关。之前我们有过一个关于《孔乙己》里面酒馆老板记账的比喻。在这个比喻里,掌柜的账本就是我们的数据文件,掌柜的临时写下的粉板就是我们的日志文件(redo log),而掌柜的记忆就相当于我们的内存。

  粉板可以记录的内容有限(redo log循环写),掌柜的记忆也是有限的,因此他总要找时间把这两部分的内容记录到账本里去。这个术语就是flush.

  当内存数据页跟磁盘数据页内容不一致的时候,我们称这个内存页为“脏页”。内存数据写入到磁盘后,内存和磁盘上的数据页的内容就一致了,称为“干净页”。

  大致上有四种情景来进行刷“脏页”:

  1. 粉板满了。(redo log写满),redo log写满以后,如果又有了新的数据,就不得不去除一些之前的数据。当然,在去除之前,要先把正确的数据记录下来才行。
  2. 生意太好,短时间内有很多客人,掌柜的记不下来了。(系统内存不足)。内存不足时,需要先淘汰掉一些数据页,如果这些数据页中有“脏页”就必须要把这些记录到磁盘才行。你可能会有疑问,为什么不直接淘汰掉这些页呢,如果再有相关的记录,就利用redo log来还原数据呢?(掌柜的疑问一部分脑子里的记忆,下次再有的话,先看账本,然后再看粉板,倒退出最新的记录),这其实是从性能方面考虑的。如果我们保证每次刷脏页一定会写磁盘,那么下次再读的时候就有两种情况。一是内存中有记录,那么直接返回。二是,内存中没有,那么先从磁盘读,读到了就直接返回。对应掌柜的就是,掌柜的有印象,直接返回。掌柜的没有印象查看账本,找到了就直接返回。
  3. 生意不太忙的时候,掌柜的闲着没事,就更新账本。(系统空闲),闲着没事,mysql就自己刷脏页玩。
  4. 年底放假,酒店关门几天。(mysql正常关闭),正常关闭时mysql会把内存的脏页都flush到磁盘上。

  回到我们开始的问题,什么情况会抖一下呢?首先排除后两种,空闲或是要关闭数据库时。来看看前两种情况:

  先是redo log写满了,要flush脏页,这种情况是innodb要尽量避免的。因为这种情况下,整个系统就不能再接受更新了,所有的更新都必须堵住。如果你从监控上看,这时的更新数会跌为0.然后是第二种情况:“内存不够用了,要先将脏页写到磁盘”。这种情况是常态。innodb用缓冲池(buffer pool)管理内存,缓冲池中的内存页有三种状态:

  1. 还没使用的
  2. 使用了并且是干净页的
  3. 使用了并且是脏页的  

  innodb的策略是尽量使用内存,因此对于一个长时间运行的库来说,未被使用的页面很少。当要读入的数据页没有在内存的时候,就必须到缓冲池中申请一个数据页。这时候只能把最久不使用的数据页从内存中淘汰掉。如果淘汰掉的是一个干净页,可以直接释放出来复用。如果淘汰掉的是一个脏页,就必须先将脏页刷到磁盘,变成干净页后才能复用。所以,刷脏页虽然是常态,但是出现以下两种情况,都会明显的影响性能:

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

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

innodb刷脏页的控制策略

  首先,你要正确地告诉innodb所在主机的io能力,这样innodb才能知道需要全力刷脏页的时候,可以刷多快。这个就要用到innodb_io_capacity这个参数了,它会告诉innodb你的磁盘能力。这个值建议你设置为磁盘的iops,磁盘的iops可以通过fio(linux)来进行测试。(linux下fio,window下可以使用iometer)

  innodb使用两个因素来进行计算刷盘速度。一个是脏页比例,一个是redo log的写盘速度。参数innodb_max_dirty_pages_pct是脏页比例上限,默认值是75%。innodb会根据当前的脏页比例m,算出一个范围在0到100之间的数字。innodb每次写入日志都有一个序号,当前写入的序号跟checkpoint对应的序号之间的差值,我们假设为n,同样,innodb会根据这个n算出一个0到100之间的数字。n越大这个计算出来的值就越大。然后,根据上述两个计算出的数据f(m)和f(n),取其中较大的值记为r,之后引擎就可以按照innodb_io_capacity定义的能力乘以r%来控制刷脏页的速度了。

  因此要合理的设置innodb_io_capacity的值,平时要多关注脏页的比例,不要让它经常接近75%。

  还有一个有趣的策略:一旦一个请求涉及到刷脏页,在准备进行刷脏页的时候,会把这个数据页旁边的数据页也检查一下,如果这个邻居也是脏页,那么就会把邻居一起刷掉。innodb使用参数innodb_flush_neighbors参数来控制这个行为,为1的时候就会触发这个“连坐”机制。在mysql8.0中,这个默认值已经是0了。

上期问题:

  如果你要维护学生信息的数据库,学生登录名统一是"学号@gamail.com",学号的规则是十五位的数字,前三位是城市编号,四到六位是学校编号,七到十位是入学年份,最后五位是顺序编号,只考虑登录验证的话,你会怎么设计这个登录名的索引呢?

  由于这个学号的规则,无论是正向还是反向的前缀索引,重复度都很高。前三位是城市编号,四到六位是学校编号,这六位数字其实是相对固定的。而邮箱后缀也是确定的,因此可以只存入学年份加顺序编号,它们的长度是9位。在此基础上,可以用数字类型来存储这个9位数字,这样只需占用4个字节,这其实是一种hash,只是用了最简单的转换规则。

  另有别的思路是,一个学校总人数不会很大,这点数据量,这个表必然是个小表。直接存原来的字符串,可以使业务更简单。这也是一种很好的回答,结合实际业务。

问题:

  一个内存配置为128gb,innodb_io_capacity设置为20000的大规格实例,正常会建议你将redo log设置成4个1gb的文件,但是如果你配置时不小心将redo log设置为了4个100m的文件,会发生什么情况呢?为什么呢?