SQL语句引起的空间暴增分析

IT那活儿发布于2023-01-11 13:20 / 2629人阅读

SQL语句引起的空间暴增分析

概述

MySQL数据库由数据文件与各类日志文件组成，通常情况下，空间增长是由数据文件、binlog文件引起的，但个别情况下是短期内MySQL产生了大量的磁盘临时表引起的。本案例就是由低效sql产生了大量磁盘临时表引起的。

分析

收到短信告警，一生产库空间使用率达到90%，随后登陆主机查看，发现空间使用率为45%，难道是误告警？为了确认告警的真实性，查看该MySQL实例占用空间情况，数据文件占用空间不高，难道业务做数据删除了？紧接着查看binlog文件占用情况，其占用空间也不高，而且该时间段产生的binlog比较少，真的是误告警？查看监控页面：

空间使用率是达到过90%，不是误告警，可随后空间也立即释放了。

查看监控

登陆监控平台，查看主机空间使用率暴涨期间，主机及数据库的性能情况和数据库本身正在进行哪些操作。

主机层面

空间使用率暴涨期间，主机负载达到了40，较正常压力值高出N多倍；cpu使用情况也较正常压力值高出很多；IO已经打满，说明这段时间内在进行大量的IO操作。

数据库层面

数据库活跃连接达到近40，也比平常高出很多，但数据库每秒的请求量却比平常低，说明数据库此时处在阻塞状态，等待后台的大量IO操作完成。

数据库会话都在进行数据排序操作，而SQL语句中排序字段选择不合适，导致产生了大量临时表，特别是因内存放不下而置换至磁盘而产生的磁盘临时表。

大量IO原因就是为了把数据从内存置换至磁盘产生的，而这批数据磁盘临时表占用了大量空间，一旦SQL执行结束或终止，相应占用的空间就会立即释放。

慢日志分析

分析相应时间段慢日志文件，发现以下SQL语句存在问题，消耗大量主机资源。

该SQL共运行154次，平均每次运行3981s，平均每次检索24670000条记录，平均每次返回2490000条记录。

该SQL语句主要存在以下问题：

查询时间字段上无索引。
查询时间跨度太大，即使有索引也未必用的上。
排序字段过多且不合理。
排序数据量大，导致排序过程中因内存有限而把数据转换至磁盘，效率太低且短时间内占用大量空间。
每次分页查询重复上次操作，且越靠后的分页查询，效率越低。

总结

数据库临时表的产生，特别是磁盘临时表，如果短时间内出现大量，导致主机空间使用率暴涨达到100%，那么相应数据库就会被hang住，无法再对外提供服务。在实际生产上避免此情况的发生，除了SQL优化外，也要定期进行主机与数据库空间的清理，时刻保持空间使用率相对比较低。当然告警的有效性也是高效手段之一。

idc机房托管超融合服务器分析sql语句性能 sql语句的优化精妙的SQL语句查询数据的sql语句

文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。

转载请注明本文地址：https://www.ucloud.cn/yun/130187.html

Redis的KEYS命令引起宕机事件

摘要：最近的互联网线上事故发生比较频繁，年月号顺丰发生了一起线上删库事件，在这里就不介绍了。最后的最后，线上操作的任何一条命令，再小心也不为过，因为由于你的一个符号而引起的事故可能是你所承担不起的。摘要：使用 Redis 的开发者必看，吸取教训啊！原文：Redis 的 KEYS 命令引起 RDS 数据库雪崩，RDS 发生两次宕机，造成几百万的资金损失作者：陈浩翔 Fundebu...

Ilikewhite 2019-08-06 14:52 评论0 收藏0
Redis的KEYS命令引起宕机事件

摘要：最近的互联网线上事故发生比较频繁，年月号顺丰发生了一起线上删库事件，在这里就不介绍了。最后的最后，线上操作的任何一条命令，再小心也不为过，因为由于你的一个符号而引起的事故可能是你所承担不起的。摘要：使用 Redis 的开发者必看，吸取教训啊！原文：Redis 的 KEYS 命令引起 RDS 数据库雪崩，RDS 发生两次宕机，造成几百万的资金损失作者：陈浩翔 Fundebu...

zoomdong 2019-08-06 13:41 评论0 收藏0
Redis的KEYS命令引起宕机事件

摘要：最近的互联网线上事故发生比较频繁，年月号顺丰发生了一起线上删库事件，在这里就不介绍了。最后的最后，线上操作的任何一条命令，再小心也不为过，因为由于你的一个符号而引起的事故可能是你所承担不起的。摘要：使用 Redis 的开发者必看，吸取教训啊！原文：Redis 的 KEYS 命令引起 RDS 数据库雪崩，RDS 发生两次宕机，造成几百万的资金损失作者：陈浩翔 Fundebu...

baoxl 2019-07-24 16:19 评论0 收藏0
Redis的KEYS命令引起宕机事件

摘要：最近的互联网线上事故发生比较频繁，年月号顺丰发生了一起线上删库事件，在这里就不介绍了。最后的最后，线上操作的任何一条命令，再小心也不为过，因为由于你的一个符号而引起的事故可能是你所承担不起的。摘要：使用 Redis 的开发者必看，吸取教训啊！原文：Redis 的 KEYS 命令引起 RDS 数据库雪崩，RDS 发生两次宕机，造成几百万的资金损失作者：陈浩翔 Fundebu...

ixlei 2019-06-25 19:14 评论0 收藏0
分析一次ORACLE数据库Session暴增的问题

摘要：问题情况一个应用程序执行失败了，在问题日志中，发现如下报错。图片描述图片描述问题分析这个报错很明显，是错误，具体的意思就是没有合适的服务处理器，我们可以上网去查一查这个错误号，基本的矛头都指向了数据库的和被占满。今天中午接到同事求助，说是一个应用里面报出了一个ORACLE错误，于是帮助他看了看，虽然最终没有解决他的问题（问题不是出在ORACLE数据库层面），还是把分析步骤发出来分享一...

bluesky 2019-07-23 16:45 评论0 收藏0
JDBC与MySQL临时表空间的分析

摘要：解决方案进一步了解到使用是为了防止查询结果集过大撑爆但是使用又会导致普通查询也生成临时表，造成临时表空间过大的问题临时表空间过大的解决方案是限制的大小，然而又导致不返回错误。背景应用 JDBC 连接参数采用 useCursorFetch=true，查询结果集存放在 mysqld 临时表空间中，导致ibtmp1 文件大小暴增到90多G，耗尽服务器磁盘空间。为了限制临时表空间的大小，设...

gityuan 2019-07-31 18:31 评论0 收藏0