MySQL临时文件导致实例磁盘空间满的解决办法

问题描述

当实例由于磁盘空间满导致自动锁定，登录控制台，依次单击基本信息>运行状态，查看运行状态。

问题原因

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

解决方案

阿里云提醒您：

• 如果您对实例或数据有修改、变更等风险操作，务必注意实例的容灾、容错能力，确保数据安全。
• 如果您对实例（包括但不限于ECS、RDS）等进行配置与数据修改，建议提前创建快照或开启RDS日志备份等功能。
• 如果您在阿里云平台授权或者提交过登录账号、密码等安全信息，建议您及时修改。

前提条件

对于MySQL 5.5、5.6、5.7版本的实例，若是云盘则可以在锁定状态删除无用数据进行解锁，在紧急情况下建议升级实例存储空间，即可解锁实例，关于如何升级实例配置，请参见变更配置。

说明：

• 本地盘在锁定状态下无法删除数据，只能通过升级实例存储空间进行解锁。
• 使用RDS实例云盘和本地盘时，建议您监控磁盘使用率，及时清理数据或进行数据拆分，使磁盘使用率不超过80%。

注意事项

清理临时文件有延迟，请耐心等待实例已使用空间的下降。

操作步骤

1. 登录RDS数据库，详情请参见通过DMS登录RDS数据库。
2. 依次选择SQL操作>SQL窗口，执行如下SQL语句，查看数据库的会话。

   show processlist

3. 单击显示结果中的State进行状态排序，在状态栏查看是否有大量Copy to tmp table 、Sending data等信息，再根据Info列的语句确定是哪个SQL语句在建立临时表，然后记下该语句的Id。
   
4. 可以通过命令行或者数据库自治服务（Database Autonomy Service，简称DAS）来终止会话。
   • 命令行
     • 在SQL窗口使用kill命令终止会话。
   • 混合云数据库管理平台
     1. 依次选择性能>空间，跳转到数据库自治服务（Database Autonomy Service，简称DAS）。
        

        说明：对于第一次进入的用户需要对DAS进行授权，若已授权，请跳到第c步。

     2. 在授权DAS访问您的云资源信息页面单击确定，并在弹出的云资源访问授权页面单击同意授权。
        
        
     3. 在实例会话中找到之前记下的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;

  系统显示类似如下。
  
• 针对binlog cache，应该少执行大事务，尤其应该减少在多个连接同时执行大事务，如果大事务比较多，可以适当调大binlog_cache_size，但是同样不应该为了节省磁盘调整这个参数，使用短连接执行大事务降低临时空间开销。

更多信息

更多实例空间满的解决方法请参见如下链接：

• 数据文件导致实例空间满的解决办法
• Binlog文件导致实例空间满的解决办法
• 系统文件导致实例空间满的解决办法

适用于

• 云数据库RDS MySQL版