MySQL创建大索引时tmpdir空间不足,修改tmpdir是否可行及操作步骤
是否推荐调整tmpdir到更大分区
该操作是解决大表创建索引时tmp空间不足报错的常规有效方案,非常值得推荐。20亿行的表创建varchar(255)索引时,MySQL排序阶段需要的临时空间通常会达到原表大小的30%~100%,默认tmpdir挂载分区空间不足时必然触发28号「磁盘空间不足」错误,更换到更大空间的分区是成本最低的解决方案之一。
调整tmpdir的潜在弊端
- 性能损耗:如果新的tmpdir挂载的是机械硬盘(HDD)而非原tmpdir所用的固态硬盘/内存盘,会直接导致索引创建速度下降30%~70%,排序阶段的IO耗时会大幅增加。如果新分区和原数据分区在同一块物理磁盘上,还会和数据读操作争抢IO资源,进一步拖慢速度。
- 权限风险:新的tmp目录如果权限配置不正确(mysql用户没有读写执行权限),会导致MySQL所有需要临时文件的操作全部失败,包括普通查询的临时表、排序、分组操作等。
- 残留临时文件风险:如果MySQL异常崩溃,调整后的tmpdir下可能残留大尺寸临时文件,不会被自动清理,长期占用磁盘空间。
- 全局影响:tmpdir是全局生效的配置,调整后所有MySQL会话的临时文件都会写入新分区,若后续有多个大操作同时执行,仍然可能占满新分区的空间。
调整MySQL tmpdir的详细步骤
分为临时调整(无需重启MySQL,仅对本次索引创建生效,适合不想影响线上业务的场景)和永久调整两种方案:
临时调整步骤(优先推荐用于本次建索引)
- 创建新的临时目录,提前确认分区剩余空间足够,建议至少预留相当于待建索引表1.5倍大小的空间:
mkdir -p /data/mysql_tmp chown mysql:mysql /data/mysql_tmp chmod 1777 /data/mysql_tmp
- 登录MySQL控制台,动态修改全局tmpdir配置:
SET GLOBAL tmpdir = '/data/mysql_tmp';
- 验证配置是否生效:
SHOW VARIABLES LIKE 'tmpdir';
确认返回路径为新设置的路径后,即可执行索引创建操作。操作完成后可以切回原tmpdir路径,避免长期影响其他业务。
永久调整步骤
- 执行上述临时调整的步骤1创建目录并配置权限。
- 编辑MySQL配置文件(通常为
/etc/my.cnf或者/etc/mysql/my.cnf),在[mysqld]段下添加/修改对应配置:
tmpdir = /data/mysql_tmp
- 重启MySQL服务生效:
systemctl restart mysqld
- 执行上述临时调整的步骤3验证配置生效。
小提示:如果是线上生产环境,优先选择临时调整方案,索引创建完成后切回原tmpdir,避免全局配置变更带来的潜在风险。如果原tmpdir是内存盘,可先评估索引创建需要的空间,空间差的不多的话优先扩容原tmpdir分区,性能损耗最小。
内容的提问来源于stack exchange,提问作者bcsta
相关产品推荐
相关产品推荐

