【AICon】探索RAG 技术在实际应用中遇到的挑战及应对策略!AICon精华内容已上线73%>>> 了解详情
写点什么

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

  • 2020-03-18
  • 本文字数:3237 字

    阅读完需:约 11 分钟

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

一、背景

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

二、现象描述

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


  • 慢查的表名都是 sbtest1,没有其他的表;

  • 大部分的慢查都是查表最新的数据,例如 select * from sbtest1 limit 1;

  • rows examined 为 1,没有扫描大量的数据。

三、问题分析

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


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


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


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



从图中,我们可以看到 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 操作,慢查消失。

3.1 聊一下 MVCC

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


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

3.1.1 Innodb 行记录

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


  • DATA_ROW_ID: 如果表没有显示定义主键,则采用 MySQL 自己生成的 ROW_ID,为 48-bit,否则表示的是用户自定义的主键值;

  • DATA_TRX_ID:表示这条记录的事务 ID。如果是二级索引,只在 page 里面保存 trx_id;

  • DATA_ROLL_PTR: 指向对应的回滚段的指针。

3.1.2 read view

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


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


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

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

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

3.1.3 判断记录可见

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

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

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

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

3.1.4 DATA_ROLL_PTR

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




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


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

3.2 慢查问题复现

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



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



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


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


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


SQL 为 select * from sbtest1 limit1


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

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

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

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

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

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

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


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


当 SQL 为 select * from sbtest1 order by id desc limit1


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

四、总结

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

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

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

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


2020-03-18 19:53735

评论

发布
暂无评论
发现更多内容

利用java实现视频人像分割及视频背景替换

夏夜许游

Java 图像分割 视频人像分割 背景替换

「一篇终结JVM」:Java面试必问十个JVM核心知识点梳理

Java全栈架构师

Java 程序员 面试 程序人生 JVM

ModelWhale 云端运行 WRF 中尺度数值气象模式,随时随地即开即用的一体化工作流

ModelWhale

数据科学 气象 全流程一体化 WRF 大气科学

技术干货|如何将 Pulsar 数据快速且无缝接入 Apache Doris

SelectDB

数据库 Doris pulsar 数据导入 kafaka

豆瓣评分9.0,“Linux命令行”经典巨著升级版上市了!

图灵教育

秀到飞起!Alibaba全新出品JDK源码学习指南(终极版)限时开源

Java全栈架构师

程序员 程序人生 jdk源码 Java 面试 架构师

从零开始搭建MySQL主从复制架构

杨杰灵

Java MySQL 主从复制

开源一夏 | 阿里云物联网平台之极速体验

六月的雨在InfoQ

阿里云 开源 物联网 8月月更

开源一夏 |我在滴滴做开源

石臻臻的杂货铺

开源 8月月更

不可忽略!户外LED显示屏的特点及优势

Dylan

LED显示屏 户外LED显示屏 led显示屏厂家

ICDAR比赛技术分享

之家技术

算法 人工只能 竞赛 ICDAR

高效的组织信息共享知识库是一种宝贵的资源

Geek_da0866

基于DMS的数仓智能运维服务,知多少?

华为云开发者联盟

数据库 后端 监控 智能运维

为何微博又双叒叕崩溃了?

华仔

融云「音视频架构实践」技术专场【内含完整PPT】

融云 RongCloud

音视频技术

出海季,互联网出海锦囊之本地化

融云 RongCloud

元宇宙 IT业界

详谈RDMA技术原理和三种实现方式

C++后台开发

网络协议 C/C++后台开发 C/C++开发 RDMA技术 以太网

可复现、开放科研、跨学科合作:数据驱动下的科研趋势及应用方案

ModelWhale

数据分析 代码复现 开放生态 协同软件 科研成果

中小微企业如何简单便捷、低成本实现数字化?360视觉云有妙招

ToB行业头条

国内首发可视化智能调优平台,小龙带你玩转KeenTune UI

OpenAnolis小助手

开源 龙蜥社区 sig KeenTune 一键式性能调优

一次做数据报表的踩坑经历,让我领略了数据同步增量和全量的区别

百思不得小赵

数据同步 增量同步 全量同步 签约计划第三季 8月月更

面试不再被吊打!这才是Redis分布式锁的七种方案的正确打开方式

程序员小毕

Java 架构 面试 分布式 分布式锁

绝无此例!用实例演示如何使用Spring搭建微服务框架

Java永远的神

spring 面试 微服务 springboot SpringCloud

浅谈Service Mesh对业务系统的价值

HelloGeek

微服务 云原生 server mesh 微服务治理

开源一夏 | 打工人的第25天-曾经的考研人

Amazing_eve

#开源

破解数字化转型困局,企业分析协同场景案例解析

ModelWhale

数据分析 数字化转型 构建模型 成功案例 协同软件

2021年数据泄露成本报告解读

SEAL安全

DevSecOps 数据安全 混合云 零信任 软件供应链

基于Java的插件化集成项目实践

阿提说说

微服务 插件化

带你了解什么是 Web3.0

liuzhen007

Web 3.0

视频人脸识别和图片人脸识别的关系

夏夜许游

人脸识别 视频人脸识别

酷开科技 × StarRocks:统一 OLAP 分析引擎,全面打造数字化的 OTT 模式

StarRocks

一次大量删除导致 MySQL 慢查的分析_文化 & 方法_Harvey_InfoQ精选文章