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

问题描述

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

解决方案

• 终止包含大量的Copy to tmp table、Sending data等信息的会话。

  1. 登录PolarDB控制台，选择集群所在地域，在集群列表中单击目标集群ID进入详情页。

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

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

     SHOW PROCESSLIST;

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

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

     kill [$ID];

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

     重要

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

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

• 调整参数。

  1. 登录PolarDB控制台，选择集群所在地域，在集群列表中单击目标集群ID进入详情页。

  2. 在配置与管理 > 参数配置中，修改参数tmp_table_size和max_heap_table_size，以增加临时表空间大小。

后续维护

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

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

  执行结果如下：

  

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

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

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