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

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

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优化外，也要定期进行主机与数据库空间的清理，时刻保持空间使用率相对比较低。当然告警的有效性也是高效手段之一。

GPU云服务器云服务器分析sql语句性能 sql语句的优化精妙的SQL语句查询数据的sql语句

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

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

Redis的KEYS命令引起宕机事件

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

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

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

ixlei 2019-06-25 19:14 评论0 收藏0