运行好好的系统突然一下收到大量的慢SQL告警

208 阅读7分钟

「这是我参与11月更文挑战的第15天,活动详情查看:2021最后一次更文挑战

问题描述

有天早上,一个运行都相当稳当的系统,突然收到大量的慢SQL告警,是一个基础数据的查询页面,通过页面展示财务的基础数据,因为是早上,财务要核对相关的数据,就会使用这个页面,在之前原来从来都没有过慢SQL,这个查询模板也很久没有发布过,怎么会突然报大量的慢SQL查询呢?

自我问题查找

遇到这种情况,首先就是登陆DBA团队提供的慢SQL查询页面,找到具体的慢SQL语句,将语句复制出来,在数据库界面查询页面上面先手动查询一下,发现确实执行的比较慢,首先就怀疑是没有走到索引,导致查询走了全文索引,这个时候就用到数据库调优利器了,explan查看执行计划,一看吓一跳,这完美的走了索引,这下脑壳蒙圈了。越到这种时候越到稳住,按照自己的节奏一步步排查问题。 果断将实例服务器的控制台打开,采用tail -f xxx.log,登录系统,点击页面查询,用了更好的定位问题,就采用单据号来查询数据,结果发现页面一直在刷新数据,查看日志一直卡在数据库那里,等待数据返回,这时候慢SQL的告警信息发过来了,过了很一会儿数据才加载出来,再次点击查询的时候,页面响应时间也非常的慢。

DBA帮忙排查

what?这剧本咋和我想象的不一样?这SQL语句查看执行计划,明明走了索引,为啥线上的就这么慢?因为使用人员催的比较急,果断给DBA打了个电话,让DBA帮忙看下这是啥问题导致的,毕竟这别人是专业的。 DBA上来的时候,了解了这SQL语句走了索引,就没有在纠结是不是SQL语句本身的问题?而是直接查看buffer pool现在的脏页占的比例,他给我们的解释是可能是因为脏页比例占据太多,就导致这个时候数据库在刷脏页到磁盘中(当时听到DBA这样说,其实自己是一脸的懵逼,也不好意思去深入的问太多,只能偷偷的将这个记下来,自己找资料了解DBA说的这个问题),DBA敲了一顿命令查看了,给的回复是空闲页比较多,数据库没有刷新脏页到磁盘。过了一会儿,他就给我截了一张图,说问题找到了,然后我将图放大一看,这服务器的CPU和IO都全部爆满,正当我在想我那应用不会有那么大的并发访问量,数据量也不大,怎么会把磁盘和IO都打满呢?正思考的时候,DBA又发了一句:你这台数据库的实例上面有另外一台数据库实例,有个哥么昨晚上上线一个需求,搞忘记给一张表加字段,导致他的应用无法使用,今早上提了工单加数据表字段,由于他那张表是个类似于日志记录信息,数据量比较大,就导致整个服务器的资源被打满,问题这找到了,DBA直接上去粗暴的把那个加字段的操作杀死,然后服务器指标一下就下来了,自己的那个访问也快起来了。

问题定位

导致这个问题总结起来就是:公司的数据库部署采用的是多实例部署方式,一台服务器上面部了很多个实例,因为数据库实例虽然是分开的,但是服务器还是一台,单个数据库实例占据资源太多会导致服务器的资源被占用,影响其他数据库的实例性能。所以在我们公司有个规定:加字段等占据资源太多的SQL工单,全部都一律在晚上8点后才能够执行。

DBA提到的刷脏页到磁盘

在问题排查中DBA提到了刷脏页到磁盘中,导致数据库性能抖动的原因,在下来的时候,自己网上找资料来终于搞懂了。其实数据库会出现性能抖动的原因主要有两点(排除外部因素):

  1. 可能Buffer Pool的缓存页满了,执行SQL的时候发现没有空闲的缓存页使用,就需要采用LRU算法淘汰一部分缓存页,但是淘汰的缓存页并不是干净页,这个时候就需要将脏页数据刷到磁盘中去,由于刷磁盘是随机读写,速度比较慢,所以在查询的时候就必须等很多缓存页刷到磁盘后,才能够腾出内存后,执行后面的操作;
  2. MySQL每次在修改数据的时候,都是改的内存数据,将修改的数据记录到redo log中,redo log是固定大小,采用环形写,可能会导致rodo log在磁盘上所有的文件写满,就需要覆盖写另外一个redo log文件吗,但是在覆盖写之前需要将那部分日志对应的更新操作改动缓存页刷新到磁盘里,这样的话就和第一点相同了,刷盘会占据很多的时间。

解决数据库抖动

有了上面的问题分析,为了解决数据库性能抖动的问题,主要有几点操作:

  1. 减少刷盘的频率

其实减少刷盘的频率,这个不是我们能够控制的,完全是MySQL自己控制哪个时候将脏页刷入到磁盘中,但是有一点业务高峰期不是每天每时每刻都会发生,总会有访问量不大的时候,这个时候MySQL就会将脏页刷入到磁盘中。所以我们可以给数据库增大内存,给BufferPool分配内存比较大一些,这样的话,在高峰期就会有很多的缓存页使用,就不会空闲页被很快用完,称到低峰期的时候把脏页刷新到磁盘里。

  1. 提升刷盘的速度

在刷盘的时候增大写入的速度,这样的话就会减少刷盘使用的时间,所以现在数据库都采用的是SSD固态硬盘增加磁盘随机IO性能,在使用SSD的时候,一般都会重新设置:

  • innodb_io_capacity参数,告诉数据库采用多大的IO速率去刷盘。这个指标可以采用FIO去测试磁盘的最大随机IO。数据库在实际flush的时候,会按照innodb_io_capacity设置值的一个百分比来进行刷磁盘,这个百分比是脏页的人比例,由参数:innodb_max_dirty_pages_pct来控制,默认值:75%
  • innodb_flflush_neighbors 这个也是比较重要的参数:flush缓存页到磁盘的时候,可能会控制把缓存页临近的其他缓存页也刷到磁盘,这样做的目的就是将临近的缓存页都刷入磁盘,因为磁盘是随机读写的,相邻的缓存页在磁盘上也是挨着的,同时刷的时候会减少寻址时间,但是这样的话有时候会导致刷盘缓存页太多,采用SSD固态硬盘的时候,它的随机读写性能特别高,这个时候就可以将值设置为:0,禁止刷临近缓存页,每次刷盘的数据量降低到最少