【项目实践05】【MySQL 临时表导致磁盘占用100%】

猫吻鱼

已于 2024-07-02 09:47:06 修改

阅读量802

点赞数 20

分类专栏：项目实践文章标签： mysql

于 2024-06-20 19:40:02 首次发布

本文链接：https://blog.csdn.net/qq_36882793/article/details/139601953

版权

项目实践专栏收录该内容

5 篇文章 0 订阅

订阅专栏

文章目录

一、前言
- 1. 文件排序
二、背景介绍
三、详细过程
四、参考内容

一、前言

本系列用来记录一些在实际项目中的小东西，并记录在过程中想到一些小东西，因为是随笔记录，所以内容不会过于详细。

1. 文件排序

在一般情况下，在需要排序的时候我们只能将记录加载到内存中然后再通过一些排序算法在内存中进行排序。有时候查询的结果集可能太大导致无法再内存中进行排序，此时就需要借助磁盘存放中间结果，在排序操作完成后再把排好序的结果集返回给客户端。而在 MySQL 中，这种在内存或磁盘中进行排序的方式统称为文件排序（filesort），但是如果 order by 字句中使用了索引列，就可能省去在内存或磁盘中排序的步骤。

通过 explain 命令的 Extra 列可以看到是否使用了文件排序。如下：

mysql> explain select * from t1 order by c;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  100 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set (0.04 sec)

注意如下使用索引排序失效的情况：

使用联合索引进行排序时也需要遵循最左匹配原则。
使用联合索引进行排序时 ASC、DESC 混用时无法使用索引进行排序。
排序列包含多个非同一索引的列也无法使用索引进行排序。
查询列和排序列并不是同一索引列也会导致无法使用索引进行排序。
排序列不能被函数修饰。

二、背景介绍

某年某月某日清晨，某台服务器磁盘告警，使用率达到100%导致服务崩溃。告警的服务器仅仅用来安装了几个中间件，包括 MySQL、RabbitMQ、Nacos等，其中可能产生大量落盘的只有 MySQL 数据落盘或者 RabbitMQ 消息堆积。

通过 find /server -type f -size +100M -exec ls -lh {} \; | awk '{print $9 ": " $5}' 查看服务器server 目录下磁盘文件大于 100M 的文件，随后发现在 MySQL 目录下多了一个 ibtmp1 文件占用了40G的大小, 很明显这是一个 InnoDB 临时表文件，直接删除，并 kill 掉引用线程。

本以为到此已经结束，但是 1min 后磁盘又再次告警，发现临时文件又重新生成了，到这里不得不排查具体问题了，如果能用到这么大的临时表文件，大概率是满足慢SQL 的查询条件，因此查询 MySQL 的慢 SQL 记录，发现存在一条SQL执行耗时40+秒，SQL并不复杂，就是一个普通的关联查询，大致如下：

select <返回字段> 
from a 
left join b on <关联关系>
left join c on <关联关系>
where
<一堆查询条件> 
order by a.name
limit xxx, xxx

然后统计了下这个查询中涉及的表的记录数量，最大数量也就 10w+, 但是 Left join 笛卡尔积之后该SQL查询结果数量超过 100w+，又因为使用了临时表，所以所有的记录都会先落到磁盘再进行排序，而这个落盘的过程就就会生成大量的文件导致磁盘 100%。

问题确定之后，便从代码中寻找执行该SQL的地方，发现是对外提供的一个三方接口，临时禁用该接口后磁盘使用恢复正常。

至此，问题临时解决了, 当然后续要针对这种情况做进一步处理。

三、详细过程

本着知其然知其所以然的过程，这里再详细模拟并复现这种情况，并对其中的一些东西做解释。

1. 问题复现

启动虚拟机，虚拟机磁盘限制20G，通过 Docker 安装一个 MySQL 实例并启动（Docker 的 MySQL安装如有需要可以参考【Docker笔记02】【常用软件安装】），MySQL 启动后磁盘占用如下：

[root@localhost data]# df -h /dev/sda3
Filesystem      Size  Used Avail Use% Mounted on
/dev/sda3        18G  9.2G  8.6G  52% /

随后建立一个临时数据库表如下，data_demo 表插入 100000 条记录，data_join 作为一个 join 表意义不大：

CREATE TABLE `data_demo` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) DEFAULT NULL,
  `author` longtext,
  `content` longtext,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=1;

CREATE TABLE `data_join` (
  `id` int(11) NOT NULL AUTO_INCREMENT
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1;

如下SQL：

SELECT
	dd.* 
FROM
	data_demo dd
	LEFT JOIN data_join dj ON 1 = 1 
ORDER BY
	dd.NAME

通过 EXPLAIN 命令分析结果如下：

mysql> explain SELECT
 dd.* 
FROM
 data_demo dd
 LEFT JOIN data_join dj ON 1 = 1 
ORDER BY
 dd.NAME;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+-----------------------------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows  | filtered | Extra                                                           |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+-----------------------------------------------------------------+
|  1 | SIMPLE      | dd    | NULL       | ALL   | NULL          | NULL    | NULL    | NULL | 99613 |   100.00 | Using temporary; Using filesort                                 |
|  1 | SIMPLE      | dj    | NULL       | index | NULL          | PRIMARY | 4       | NULL |     2 |   100.00 | Using where; Using index; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+-----------------------------------------------------------------+
2 rows in set (0.06 sec)

可以看到这里的对 dd 表是全表查询，并且使用了临时表（Using temporary）。

SQL 执行时监控磁盘空间占用情况，大致如下：

[root@localhost data]# df -h /dev/sda3
Filesystem      Size  Used Avail Use% Mounted on
/dev/sda3        18G  9.2G  8.6G  52% /
[root@localhost data]# df -h /dev/sda3
Filesystem      Size  Used Avail Use% Mounted on
/dev/sda3        18G  9.8G  8.0G  55% /
[root@localhost data]# df -h /dev/sda3
Filesystem      Size  Used Avail Use% Mounted on
/dev/sda3        18G   10G  7.8G  57% /
[root@localhost data]# df -h /dev/sda3
Filesystem      Size  Used Avail Use% Mounted on
/dev/sda3        18G   11G  7.1G  61% /
[root@localhost data]# df -h /dev/sda3
Filesystem      Size  Used Avail Use% Mounted on
/dev/sda3        18G   12G  6.2G  66% /
[root@localhost data]# df -h /dev/sda3
Filesystem      Size  Used Avail Use% Mounted on
/dev/sda3        18G   13G  5.0G  72% /
[root@localhost data]# df -h /dev/sda3
Filesystem      Size  Used Avail Use% Mounted on
/dev/sda3        18G   15G  3.5G  81% /
[root@localhost data]# df -h /dev/sda3
Filesystem      Size  Used Avail Use% Mounted on
/dev/sda3        18G   16G  2.4G  87% /

可以看到磁盘空间的使用变化，通过命令查看 MySQL data 目录下的数据如下：

[root@localhost data]# find ./ -type f -size +100M -exec ls -lh {} \; | awk '{print $9 ": " $5}'
./demo/data_demo.ibd: 3.2G
./ibtmp1: 6.3G

可以看到这里因为执行上面的SQL 生成了一个 6.3G 的临时文件。

2. 解决方案

上面虽然没有复现出磁盘 100% ，但是现象已经出现了。那么开始提供解决方案（这里（~~不想写了~~ ）直接参考 MYSQL的ibtmp1文件太大问题）：

设置innodb_temp_data_file_path选项，设定文件最大上限（innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:200M），超过上限时，需要生成临时表的SQL无法被执行（一般这种SQL效率也比较低，可借此机会进行优化）。
检查INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO，找到最大的临时表对应的线程，kill之即可释放，但ibtmp1文件则不能释放（除非重启）。
择机重启实例，释放ibtmp1文件，和ibdata1不同，ibtmp1重启时会被重新初始化而ibdata1则不可以。
定期检查运行时长超过N秒（比如N=300）的SQL，考虑清理，避免垃圾SQL长时间运行影响业务。

3.补充

ibtmp1是非压缩的innodb临时表的独立表空间,通过innodb_temp_data_file_path参数指定文件的路径，文件名和大小，默认配置为ibtmp1:12M:autoextend，也就是说在默认情况下支持大文件的系统这个文件大小是可以无限增长的。

演示 SQL 如果要使用临时表 JOIN 和 ORDER BY 这两个条件缺一不可。关于 Explain 命令的分析可以参考【MySQL01】【 Explain 命令详解】。

如果不排序，则会使用 join buffer 来完成查询如下：

mysql> explain SELECT
 dd.* 
FROM
 data_demo dd
 LEFT JOIN data_join dj ON 1 = 1;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+-----------------------------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows  | filtered | Extra                                                           |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+-----------------------------------------------------------------+
|  1 | SIMPLE      | dd    | NULL       | ALL   | NULL          | NULL    | NULL    | NULL | 99613 |   100.00 | NULL                                                            |
|  1 | SIMPLE      | dj    | NULL       | index | NULL          | PRIMARY | 4       | NULL |     2 |   100.00 | Using where; Using index; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+-----------------------------------------------------------------+
2 rows in set (0.05 sec)

如果不 JOIN ，则会使用 filesort 来完成查询，如下：

mysql> explain SELECT
 dd.* 
FROM
 data_demo dd
 order by dd.name;
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------+
|  1 | SIMPLE      | dd    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 99613 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------+
1 row in set (0.06 sec)

ibtmp1 文件删除后，磁盘空间并没有减少，因为 MySQL 线程并没有释放该文件，通过 ls -ld /proc/*/fd/* 2>&1 | fgrep '(deleted)' 命令可以看到，可以重启数据库实例或者kill 掉对应线程（除非你能承担 kill 线程的后果，否则还是别 kill ）来释放磁盘文件如下：

# 查看磁盘占用情况
[root@localhost data]# df -h /dev/sda3
Filesystem      Size  Used Avail Use% Mounted on
/dev/sda3        18G   16G  2.4G  87% /
[root@localhost data]# find ./ -type f -size +100M -exec ls -lh {} \; | awk '{print $9 ": " $5}'
./demo/data_demo.ibd: 3.2G
./ibtmp1: 6.3G
# 删除 ibtmp1 文件
[root@localhost data]# rm -rf ibtmp1 
[root@localhost data]# find ./ -type f -size +100M -exec ls -lh {} \; | awk '{print $9 ": " $5}'
./demo/data_demo.ibd: 3.2G
[root@localhost data]# df -h /dev/sda3
Filesystem      Size  Used Avail Use% Mounted on
/dev/sda3        18G   16G  2.4G  87% /
# 查看未释放的线程
[root@localhost data]# ls -ld /proc/*/fd/* 2>&1 | fgrep '(deleted)'
lrwx------. 1 root    root   64 Jun 12 00:04 /proc/1124/fd/8 -> /tmp/#19097707 (deleted)
lrwx------. 1 gdm     gdm    64 Jun 12 00:04 /proc/1805/fd/19 -> /tmp/#19097711 (deleted)
lrwx------. 1 gdm     gdm    64 Jun 12 00:04 /proc/1835/fd/6 -> /memfd:pulseaudio (deleted)
lrwx------. 1 polkitd input  64 Jun 12 00:48 /proc/3955/fd/10 -> /var/lib/mysql/ibtmp1 (deleted)
lrwx------. 1 polkitd input  64 Jun 12 00:48 /proc/3955/fd/11 -> /tmp/ibhIiZj4 (deleted)
lrwx------. 1 polkitd input  64 Jun 12 00:48 /proc/3955/fd/4 -> /tmp/ibrOtXrz (deleted)
lrwx------. 1 polkitd input  64 Jun 12 00:48 /proc/3955/fd/5 -> /tmp/ib7GO1bt (deleted)
lrwx------. 1 polkitd input  64 Jun 12 00:05 /proc/3955/fd/6 -> /tmp/ibhp66Vm (deleted)
lrwx------. 1 polkitd input  64 Jun 12 00:48 /proc/3955/fd/7 -> /tmp/ibabGita (deleted)
lrwx------. 1 root    root   64 Jun 12 00:04 /proc/724/fd/8 -> /tmp/#16777285 (deleted)
# 非必要别 kill， 否则不知道啥问题会出现，这里做个演示而已
[root@localhost data]# kill 3955
# 磁盘已经释放
[root@localhost data]# df -h /dev/sda3
Filesystem      Size  Used Avail Use% Mounted on
/dev/sda3        18G  9.1G  8.7G  52% /

四、参考内容

https://www.ucloud.cn/yun/129633.html
https://blog.csdn.net/ishulei/article/details/126282775

猫吻鱼

关注

20
点赞
踩
15

收藏

觉得还不错? 一键收藏
打赏
0
评论
【项目实践05】【MySQL 临时表导致磁盘占用100%】

本系列用来记录一些在实际项目中的小东西，并记录在过程中想到一些小东西，因为是随笔记录，所以内容不会过于详细。某年某月某日清晨，某台服务器磁盘告警，使用率达到100%导致服务崩溃。告警的服务器仅仅用来安装了几个中间件，包括 MySQL、RabbitMQ、Nacos等，其中可能产生大量落盘的只有 MySQL 数据落盘或者 RabbitMQ 消息堆积。通过。
复制链接

扫一扫