AWS Aurora MySQL写/rdsdbdata/tmp报错(Errcode:28)处理及优化咨询

参数不生效问题排查 #

• tmp_table_size和max_heap_table_size两个参数必须设置为相同值，MySQL最终会取两者的较小值作为实际内存临时表上限
• 这两个参数属于静态参数，修改RDS参数组后需要重启实例才能全局生效，未重启前仅新建立的会话可能生效，已有会话仍沿用旧值

SQL优化方案 #

• 添加联合索引：针对排序字段创建联合索引，执行ALTER TABLE student_grade ADD INDEX idx_id_date_desc (id, date DESC)，创建完成后查询可以直接利用索引的有序性返回结果，完全跳过临时表创建和排序步骤，是该场景最优解决方案
• 拆分大查询为分页查询：2亿条记录全量查询本身风险极高，建议按id范围拆分查询，示例语句：SELECT id, name, date, score FROM student_grade WHERE id BETWEEN {起始id} AND {结束id} ORDER BY id, date DESC，每次查询10-100万条数据，分批拉取后在业务侧合并结果，彻底避免大内存占用
• 裁剪查询字段：如果查询结果中存在非必要字段，直接删除SELECT后的对应字段，可显著降低临时表占用的空间大小

可调整的MySQL配置项 #

• 临时表内存上限：你的全量查询结果约500MB，可将tmp_table_size和max_heap_table_size统一设置为640MB（预留20%冗余即可），注意该值最大不要超过实例总内存的25%，避免实例出现OOM风险，通用的1%内存建议仅适用于常规小查询场景，大查询场景可按需适当调大
• 排序缓冲区大小：调整sort_buffer_size参数到4MB-8MB，该参数为会话级排序专用内存，足够的排序内存可以减少排序过程中磁盘临时文件的写入需求，注意不要调至超过32MB，并发过高时会导致内存占用爆炸
• 磁盘临时表配置：可让云团队检查internal_tmp_disk_storage_engine参数，若使用InnoDB作为磁盘临时表引擎，确认innodb_temp_data_file_path没有配置最大容量限制，允许临时表文件自动扩展

补充说明：innodb_file_per_table参数仅作用于普通用户表，和临时表的大小限制没有关联，你之前的猜测不成立。

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