一次大量删除导致MySQL慢查的分析

栏目: 数据库 · 发布时间: 5年前

内容简介:监控上收到了大量慢查的告警,业务也反馈查询很慢,随即打开电脑确认慢查的原因。通过平台的慢查分析之后,我们发现慢查有以下特征:通对慢查的大致分析,SQL本身没有发现问题。那么是不是主机或者网络等有问题呢?

背景

监控上收到了大量慢查的告警,业务也反馈查询很慢,随即打开电脑确认慢查的原因。

现象描述

通过平台的慢查分析之后,我们发现慢查有以下特征:

  1. 慢查的表名都是sbtest1,没有其他的表
  2. 大部分的慢查都是查表最新的数据,例如select * from sbtest1 limit 1;
  3. rows examined 为1,没有扫描大量的数据

问题分析

通对慢查的大致分析,SQL本身没有发现问题。那么是不是主机或者网络等有问题呢?

经过对网络和主机磁盘的IO等的分析,负载均正常,没有丢包的现象。

回到数据库本身,慢查还在,确认下慢查到底是慢在哪里。

当慢查在执行的时候,大部分的都是表现在 Sending data的状态,我们通过profiling去确认下慢查的时间分布:

一次大量删除导致 <a href='https://www.codercto.com/topics/18746.html'>MySQL</a> 慢查的分析

从图中,我们可以看到 sending data 耗费的时间为0.945秒,基本占据了 SQL 执行时间的99%。

那么 sending data 是什么意思呢,我们从官方文档里面了解下。

The thread is reading and processing rows for a SELECT statement, and sending data to the client. Because operations occurring during this state tend to perform large amounts of disk access (reads), it is often the longest-running state over the lifetime of a given query.

Sending data表示在读取以及处理行数据以及发送数据到客户端,由于数据只有一行,且当时网络确认正常,那么时间就是耗费在读取和处理select的数据。

那为啥只取limit 1,而且没有where条件的SQL执行扫描一行数据会这么慢呢?

打开监控,看看有没有啥指标异常。

我们注意到数据库的 History list length 这个指标一直在升高,达到了几万。慢查的执行时间是随着 History list length 升高而变的更慢。当 History list length 一直居高不下的时候,说明了有大量的UNDO没有被purge。由于当前数据库的隔壁级别是RR,开始比较早的事务,如果还没提交,就需要通过UNDO去构建对应版本历史时,保证数据库的可重复读(跟MVCC有关)。

既然 History list length 那么高,可能是有历史事务出现异常没有提交,也有可能是一致性快照的备份。可以通过information_schema.innodb_trx 表去确认对应的事务信息。经过查询,的确发现一个事务执行了4个小时左右,没有提交,且不是备份用户。手动将该线程执行kill操作,慢查消失。

聊一下MVCC

MySQL InnoDB 支持 MVCC 多版本,可以在普通的 SELECT 时不加锁。利用多版本读取指定版本的行记录,降低加锁的次数,能极大提高数据库的并发读写能力。

Innodb 在事务的某个时刻记录下MySQL所有的活跃事务列表,保存到 read view 里面。在之后的查询中,通过比较记录的事务ID和 read view 里面的事务列表,判断记录是否可见。

Innodb 行记录

在Innodb的行结构中,还存在三个系统列,分别是 DATA_ROW_IDDATA_TRX_IDDATA_ROLL_PTR

DATA_ROW_ID
DATA_TRX_ID
DATA_ROLL_PTR

read view

read view 是在SQL语句执行之前申请的,其中RC隔离级别是每个SELECT都会申请,RR隔离级别的 read view 是事务开始之后的第一个SQL申请,之后事务内的其他SQL都使用该 read view

read view 中有三个变量需要重点关注:

  1. low_limit_id : 表示的是创建 read view 那一刻活跃的事务列表的最大的事务ID

  2. up_limit_id :表示的是创建 read view 那一刻活跃的事务列表的最小的事务ID

  3. trx_ids :表示的创建 read view 那一刻所有的活跃事务列表。

判断记录可见

  1. 当记录的 DATA_TRX_ID 小于 read vewup_limit_id ,说明该记录在创建 read view 之前就已经提交,记录可见

  2. 如果记录的 DATA_TRX_ID 和事务创建者的 TRX_ID 一样,记录可见

  3. 当记录的 DATA_TRX_ID 大于 read vewup_limit_id ,说明该记录在创建 read view 之后进行的新建事务修改提交的,记录不可见

  4. 在RR隔离级别,如果A事务在B事务创建 read view 之前开始的,那么B事务里面的SQL是不能看到A事务执行的修改的。因此还有一条规则:如果记录对应的 DATA_TRX_IDread viewtrx_ids 里面,那么该记录也是不可见的。

DATA_ROLL_PTR

UNDO日志是MVCC的重要组成部分,当一条数据被修改时,UNDO日志里面保存了记录的历史版本。当事务需要查询记录的历史版本时,可以通过UNDO日志构建特定版本的数据

一次大量删除导致MySQL慢查的分析

一次大量删除导致MySQL慢查的分析

每条行记录上面都有一个指针 DATA_ROLL_PTR ,指向最近的UNDO记录。同时每条UNDO记录包含一个指向前一个UNDO记录的指针,这样就构成了一条记录的所有UNDO历史的链表。当UNDO的记录还存在,那么对应的记录的历史版本就能被构建出来。

当记录对应的版本通过 DATA_TRX_ID 比对发现不可见时,通过系统列 DATA_ROLL_PTR ,找到对应的回滚段记录,继续通过上述判断记录可见的规则,进行判断,如果记录依旧不可见,继续通过回滚段查找之前的版本,直到找到对应可见的版本。

慢查问题复现

经过和业务方沟通,得知该表每天都有定时任务,会删除历史数据。大致了解到整个过程后,我们搭建模拟环境进行测试。

一次大量删除导致MySQL慢查的分析

测试分为三个session,其中Sess1执行长事务,没有提交。Sess2对表的历史数据做清理,清理了2000万的数据。此时在Sess3执行查询,快慢情况如上图所示。 select * from sbtest1 limit 1 跟预期表现一样,为很慢。但是 select * from sbtest1 order by id desc limit 1 执行很快,这是为什么呢?

一次大量删除导致MySQL慢查的分析

上图为主键的记录格式,在每条主键记录的前面有个删除标志位,然后是主键ID,事务ID,回滚段指针,最后是行记录。

当记录被执行删除的时候,MySQL只是将记录标记为已删除,同时更新 DATA_TRX_ID 为自己删除会话的事务ID,并没有将记录真正删除。当被删除的记录不再被其他事务需要的时候,会被 purge线程 删除。 purge线程 负责清理这些真正被删除的记录以及不再需要的UNDO日志。

回到慢查本身,我们来看看慢查的执行过程。

SQL为 select * from sbtest1 limit 1

  1. 通过主键,扫描到ID=1的记录,根据MVCC比对,发现自己的事务ID大于记录的DATA TRX ID,匹配可见规则1,记录可见

  2. 由于ID=1已经被标记为DELETED,删除记录可见

  3. 由于表数据还没有全部扫描完成,未满足limit 1,继续扫描下一条记录

  4. 扫描到ID=2的记录,根据MVCC比对,发现自己的事务ID大于记录的 DATA_TRX_ID ,匹配可见规则1,记录可见

  5. 由于ID=2已经被标记为DELETED,删除记录可见

  6. 由于表数据还没有全部扫描完成,未满足limit 1,继续扫描下一条记录

  7. 重复4-6步骤,直到满足找到一条记录,或者全表扫描完成

由于被删除的记录有2000万,Innodb 需要扫描2000万的记录,才能找到符合条件的第一条记录,然后返回到MySQL的Server层。

当SQL为 select * from sbtest1 order by id desc limit 1

由于删除的是老数据,当从ID最大的方向开始扫描时,通过MVCC 判断可见,然后判断记录是否被标记为删除的时候,记录没有被删除,因此就可以快速返回到Server层,SQL执行效率就会很高。

总结

  1. 当新的会话执行SQL的时候,如果扫描区间范围内有大量被标记为删除的记录,会导致SQL执行效率变低,通过二级索引去查询也是类似的。例如当 where c1=1 有10万match 的记录时,其中扫描方向的90%的记录都被标记为以及删除,但是还没purge,执行 where c1 =1 limit 1 一样会慢。

  2. 如果一个会话没有开启自动提交,那么查到的记录就是会话开始的时候的第一个SQL执行的快照。如果查询的行记录的历史版本过多,将导致通过UNDO去构建历史版本的时间过长,对于高负载的OLTP系统来说是个灾难。

  3. 尽可能的避免长事务,监控长事务的个数,进行告警,及时kill会话

  4. 已经提交的事务的UNDO只有在隔离级别是RR的情况下才会被比自己早的事务使用,如果隔离级别是RC,提交的事务的UNDO会很快被purge,就不会有本文上面出现的问题。

欢迎关注我们的公众号

一次大量删除导致MySQL慢查的分析

以上所述就是小编给大家介绍的《一次大量删除导致MySQL慢查的分析》,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对 码农网 的支持!

查看所有标签

猜你喜欢:

本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们

计算机网络(第5版)

计算机网络(第5版)

Andrew S. Tanenbaum、David J. Wetherall / 严伟、潘爱民 / 清华大学出版社 / 2012-3-1 / 89.50元

本书是国内外使用最广泛、最权威的计算机网络经典教材。全书按照网络协议模型自下而上(物理层、数据链路层、介质访问控制层、网络层、传输层和应用层)有系统地介绍了计算机网络的基本原理,并结合Internet给出了大量的协议实例。在讲述网络各层次内容的同时,还与时俱进地引入了最新的网络技术,包括无线网络、3G蜂窝网络、RFID与传感器网络、内容分发与P2P网络、流媒体传输与IP语音,以及延迟容忍网络等。另......一起来看看 《计算机网络(第5版)》 这本书的介绍吧!

JSON 在线解析
JSON 在线解析

在线 JSON 格式化工具

在线进制转换器
在线进制转换器

各进制数互转换器

RGB CMYK 转换工具
RGB CMYK 转换工具

RGB CMYK 互转工具