由于临时文件过多导致集群存储空间被占满的解决办法

问题描述

PolarDB MySQL版集群可能会由于查询语句的排序、分组、关联表产生临时表文件，或大事务未提交前产生Binlog cache文件，从而出现本地空间占用过大或者占满的情况，产生报错如“The table '/home/mysql/log/tmp/#sql_xxx' is full”。

解决方案

1. 登录PolarDB控制台。
2. 在控制台左上角，选择集群所在地域。
3. 找到目标集群，单击集群ID。

4. 单击基本信息页面右上角的登录数据库。

5. 在目标数据库中，执行以下SQL语句，查看数据库会话情况。

   SHOW PROCESSLIST

6. 单击查询结果的State列，对该列的数据进行排序，查看是否有大量的Copy to tmp table、Sending data等信息，并记录该会话的ID值。

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

   kill [$ID];

   [$ID]为步骤6记录的ID值。

   重要

   执行终止会话操作前，请确保该会话不会影响正常运行的业务。

若通过以上步骤仍然不能释放存储空间，您可以重启集群中的各个节点，来释放临时文件占用的存储空间。重启节点的操作步骤请参见重启节点。

后续维护

• 针对查询过程中产生的临时文件，应该优化查询语句，如在查询语句中避免频繁使用Order By、Group By操作等。您可以通过Explain加SQL语句查看是否使用内部临时表，示例如下：

  EXPLAIN SELECT * FROM alarm GROUP BY created_on ORDER BY default;

  执行结果如下：

  

  在Extra列存在Using temporary字样，则表示执行该SQL语句时会使用内部临时表。

• 针对binlog cache，应该减少执行大事务的情况，尤其应该减少在多个连接同时执行大事务的情况，如果大事务比较多，建议使用短连接执行大事务，以降低临时空间开销。

• 建议您定时监控存储空间使用率，及时清理数据或进行数据拆分，使存储空间使用率不超过80%。