在超大规模MySQL Aurora RDS表创建索引时遇临时文件写入失败错误

针对7.5亿行无写入的大表创建索引时出现Error Code: 1878. Temporary file write failure.，仅调整tmp_table_size和max_heap_table_size无效的情况，可从以下几个方向排查解决：

• 核查临时存储的可用空间
  Aurora创建索引时的临时文件默认存储在实例的本地临时存储（部分实例类型为NVMe SSD），7.5亿行数据的排序操作需要的临时空间可能远超该存储的容量。执行SHOW VARIABLES LIKE 'tmpdir';查看临时文件路径，通过实例监控确认该路径的剩余空间。若本地存储不足，可升级至临时存储更大的实例类型，或通过参数组将tmpdir配置到有足够剩余空间的EBS卷（需确保EBS卷IOPS满足需求）。

• 调整排序相关参数
  除了已调整的两个参数，重点优化以下配置：

  • sort_buffer_size：控制排序操作的内存缓冲区，建议根据实例内存设置为256M-1G（避免过大导致内存耗尽），减少排序时的临时文件生成量；
  • read_rnd_buffer_size：提升排序后随机读的效率，间接降低临时文件的持续写入压力，可设置为64M-128M。
    修改参数需通过RDS参数组生效，重启实例后验证。

• 采用分批创建索引的策略
  直接全表创建索引会触发大规模排序，临时文件压力极大，可尝试以下分批方案：

  1. 分区表分批处理：先按自增ID、时间等字段将原表分区，逐个分区添加索引，完成后合并分区；
  2. 新表分批迁移：创建与原表结构一致的新表并提前建好索引，分批将原表数据插入新表（例如每次插入100万行），全部迁移完成后通过RENAME TABLE替换原表。示例语句：

     INSERT INTO new_large_table SELECT * FROM old_large_table WHERE id BETWEEN 1 AND 1000000;
     

• 检查集群存储剩余量
  Aurora集群存储接近饱和时，也会导致临时文件写入失败。执行SHOW GLOBAL STATUS LIKE 'AuroraVolumeBytesUsed';查看已用存储，确保剩余空间不低于原表数据量的20%-30%，不足则扩容集群存储。

• 升级实例内存规格
  若实例内存过小，即使调大参数也无法分配足够的内存用于排序，会更早触发临时文件写入。7.5亿行的大表操作建议使用内存型实例（如db.r5系列），确保内存能支撑部分排序操作在内存中完成，减少临时文件的依赖。

内容的提问来源于stack exchange，提问作者Carter Jasinski