1114 aws runtime_error ai_generated true

MySQL server has gone away: error 1114 (HY000) The table '/rdsdbdata/tmp/#sql_xxx' is full

ID: aws/rds-storage-full

Also available as: JSON · Markdown · 中文
85%Fix Rate
82%Confidence
1Evidence
2023-08-20First Seen

Version Compatibility

VersionStatusIntroducedDeprecatedNotes
rds-mysql-8.0.35 active — — —
rds-mariadb-10.6.16 active — — —

Root Cause

RDS instance storage is exhausted, causing temporary tables or data files to fail writes; often due to insufficient allocated storage or unoptimized queries generating large temp tables.

generic

中文

RDS 实例存储已耗尽，导致临时表或数据文件写入失败；通常是由于分配的存储不足或未优化的查询生成了大量临时表。

Official Documentation

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_Troubleshooting.html#CHAP_Troubleshooting.StorageFull

Workarounds

  1. 90% success Modify RDS instance to increase allocated storage: `aws rds modify-db-instance --db-instance-identifier mydb --allocated-storage 200 --apply-immediately` 
    Modify RDS instance to increase allocated storage: `aws rds modify-db-instance --db-instance-identifier mydb --allocated-storage 200 --apply-immediately`
  2. 75% success Clean up old data or logs: delete unused tables, truncate binary logs with `CALL mysql.rds_rotate_binlog;` or reduce binlog retention. 
    Clean up old data or logs: delete unused tables, truncate binary logs with `CALL mysql.rds_rotate_binlog;` or reduce binlog retention.
  3. 65% success Optimize queries to use indexes and avoid large temp tables; set tmp_table_size and max_heap_table_size to allow in-memory temp tables. 
    Optimize queries to use indexes and avoid large temp tables; set tmp_table_size and max_heap_table_size to allow in-memory temp tables.

中文步骤

  1. 修改 RDS 实例以增加分配的存储：`aws rds modify-db-instance --db-instance-identifier mydb --allocated-storage 200 --apply-immediately`
  2. 清理旧数据或日志：删除未使用的表，使用 `CALL mysql.rds_rotate_binlog;` 轮换二进制日志或减少 binlog 保留时间。
  3. 优化查询以使用索引并避免大型临时表；设置 tmp_table_size 和 max_heap_table_size 以允许内存临时表。

Dead Ends

Common approaches that don't work:

  1. Restart the RDS instance to clear temporary tables 70% fail

    Restarting may free temp space temporarily, but if storage is full, the root cause (disk space) persists and queries will fail again.

  2. Increase innodb_buffer_pool_size to reduce disk I/O 85% fail

    Buffer pool size affects memory, not disk storage; it won't free up space on the volume.