RDS MySQL临时文件导致实例磁盘空间满且出现“锁定中”状态

**问题描述**

阿里云云数据库RDS MySQL实例由于临时文件占用磁盘空间较多，导致实例的运行状态为“锁定中”。

![](/media/202210//1665623615.2019267.png)

## 问题原因

MySQL实例可能会由于查询语句的排序、分组、关联表产生的临时表文件，或者大事务未提交前产生的binlog cache文件，导致实例磁盘空间满。为避免数据丢失，RDS会将实例锁定，在锁定之后，将无法进行写入操作。

## 解决方案

### 操作步骤

对于MySQL 5.5、5.6、5.7和8.0版本的实例，在紧急情况下建议扩容实例存储空间，扩容后需要耐心等待一段时间（5分钟左右），方可解锁实例。

若无法扩容实例存储空间，参见以下方法处理：

1. 通过DMS连接实例
2. 执行以下SQL语句，查看数据库的会话。

show processlist

1. 单击显示结果中的**State**，进行状态排序，在状态栏查看是否有大量“Copy to tmp table”、“Sending data”等信息，再根据右侧Info列确定是哪个SQL语句在建立临时表，然后记录该语句的ID值。

![](/media/202210//1665623615.2742174.png)

1. 执行以下SQL语句，终止会话。

kill [$ID];

**说明**：[$ID]为上一步获取的ID值，注意确认终止该会话不会影响业务

### 后续维护

若锁定问题已解决，请参考以下步骤，预防再次出现锁定问题：

* 在资源不足时，实例自动扩容存储空间。
* 针对查询产生的临时文件，应该优化SQL语句，避免频繁使用order by、group by操作，可以适当的将**tmp\_table\_size**和**max\_heap\_table\_size值调大**，但是为了减少磁盘使用而调高tmp\_table\_size和max\_heap\_table\_size并不明智，因为内存资源远比磁盘资源宝贵。您可以通过explain加SQL语句查看是否使用内部临时表，示例如下，在Extra字段中有“Using temporary”字样，则代表会使用内部临时表。

explain select * from alarm group by created\_on order by default;

系统显示类似如下。  
![](/media/202210//1665623615.2996743.png)

* 针对binlog cache，应该减少执行大事务的情况，尤其应该减少在多个连接同时执行大事务的情况，如果大事务比较多，可以适当将binlog\_cache\_size值调大，但是同样不建议为了节省磁盘空间调整这个参数，建议使用短连接执行大事务，降低临时空间开销。
* 建议监控磁盘使用率，及时清理数据或进行数据拆分，使磁盘使用率不超过80%。