AWS Aurora MySQL临时表满（Error 1114）问题排查求助

我们使用配置16GB内存的AWS Aurora MySQL(8.0.mysql_aurora.3.04.0) InnoDB引擎只读实例，对一张250GB未分区大表执行包含min、max、窗口函数、group by等计算的SELECT查询时，执行数秒后抛出错误：

SQL Error [1114] [HY000]: The table '/rdsdbdata/tmp/#sql161_17a011_a' is full

已尝试的操作：

• 将@@temptable_max_ram、@@temptable_max_mmap从1GB调至2GB
• 设置会话参数：

SET session aurora_tmptable_enable_per_table_limit = ON;
SET session tmp_table_size =  134217728; 
SET session max_heap_table_size =  134217728; 

• 更新表统计信息

但问题依旧，另外执行以下命令返回空结果：

show variables like '%Created_tmp_disk_tables%';
show variables like '%Created_tmp_tables%';

当前相关配置：

innodb_file_per_table   = ON
innodb_data_file_path   = ibdata1:12M:autoextend
internal_tmp_mem_storage_engine = TempTable

作为MySQL新手，想了解：

1. 是否遗漏了某些配置？
2. 如何在查询运行时查看临时表使用量以确定所需大小及解决该错误？

解答 #

1. 可能遗漏的配置及调整建议 #

• 检查临时磁盘分区空间：错误提示的/rdsdbdata/tmp/分区可能已满，先通过操作系统命令确认：

  df -h /rdsdbdata/tmp
  

  若空间不足，可手动清理未被占用的#sql*临时文件，或联系AWS调整该分区的存储空间。

• 调整内存临时表大小限制：你当前设置的tmp_table_size和max_heap_table_size仅128MB，对于250GB大表的复杂查询来说，内存临时表会快速写满并转磁盘，加剧磁盘临时表的压力。建议将两者调至4GB（不超过实例内存的25%，避免OOM）：

  SET session tmp_table_size = 4294967296;
  SET session max_heap_table_size = 4294967296;
  

  注意：这两个参数取最小值生效，必须保持一致。

• 检查Aurora专属临时表限制：Aurora的aurora_tmp_table_max_size参数控制单个磁盘临时表的最大大小，默认值可能过小。查看并调整：

  SHOW VARIABLES LIKE 'aurora_tmp_table_max_size';
  SET session aurora_tmp_table_max_size = 10737418240; -- 10GB，根据磁盘空间灵活调整
  

• 关闭单表临时表内存限制：你开启的aurora_tmptable_enable_per_table_limit会给每个临时表单独设置内存上限，可能导致内存临时表提前转磁盘，建议先关闭：

  SET session aurora_tmptable_enable_per_table_limit = OFF;
  

• 检查InnoDB临时表空间配置：如果磁盘临时表使用InnoDB引擎，innodb_temp_data_file_path控制其存储空间，默认的ibtmp1:12M:autoextend可能因磁盘分区限制无法扩展。查看参数：

  SHOW VARIABLES LIKE 'innodb_temp_data_file_path';
  

  如需修改，需通过Aurora参数组调整后重启实例。

2. 查询运行时查看临时表使用量的方法 #

• 通过进程列表跟踪：查询运行时，查看进程状态判断临时表创建情况：

  SELECT id, user, db, command, time, state, info FROM INFORMATION_SCHEMA.PROCESSLIST WHERE info IS NOT NULL;
  

  若state列显示Creating tmp table或Copying to tmp table，说明正在生成临时表。

• 用Performance Schema监控细节：开启相关监控项后，可跟踪查询的临时表使用统计：

  1. 开启监控：

     UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME LIKE '%tmp_table%';
     UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%events_statements%';
     

  2. 查询目标查询的临时表数据：

     SELECT * FROM performance_schema.events_statements_history_long WHERE SQL_TEXT LIKE '%你的查询关键词%'\G
     

     结果中的CREATED_TMP_DISK_TABLES、CREATED_TMP_TABLES字段会显示该查询创建的临时表数量。

• 查看磁盘临时表实时大小：登录实例操作系统，直接查看临时表文件的大小：

  ls -lh /rdsdbdata/tmp/#sql*
  

• Aurora CloudWatch指标：在AWS CloudWatch控制台查看实例的TemporaryTableUsage指标，包括TemporaryTableMemoryUsage（内存临时表使用量）和TemporaryTableDiskUsage（磁盘临时表使用量）。

额外优化建议 #

• 优化查询语句：复杂的窗口函数和group by易产生大量临时表，可尝试：

  • 只保留查询必需的字段，减少数据量
  • 给group by、窗口函数的分区/排序字段添加索引，避免全表扫描和排序操作
  • 按查询常用维度（如时间）对大表进行分区，降低单次查询的数据扫描范围

• 分析查询执行计划：用EXPLAIN ANALYZE查看查询计划，确认是否存在Using temporary、Using filesort等低效操作，针对性优化：

  EXPLAIN ANALYZE SELECT ...; -- 替换为你的查询语句
  

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