Mysql optimize table 时报错:Temporary file write fail的解决

当我们执行 optimize table xxx ; 尝试进行碎片整理时，也可能报错 Temporary file write failure.

Tips： MySQL 8.x

查看mysql日志文件，也会发现报错 [InnoDB] Error number 28 means ‘No space left on device’

[ERROR] [MY-012639] [InnoDB] Write to file (merge) failed at offset 5405409280, 1048576 bytes should have been written, only 114688 were written. Operating system error number 28. Check that your OS and file system support files of this size. Check also that the disk is not full or a disk quota exceeded.2022-05-10T14:00:58.817066+08:00 2272191 [ERROR] [MY-012640] [InnoDB] Error number 28 means ‘No space left on device’

但是实际上我的 MySQL 的数据盘空间是足够保存这个表的两倍空间的，那么则说明这个报错（No space left on device）说的是系统盘的空间。

Tips: 由于在 MySQL 5.6.7 之后推出了 Online DDL ，所以我执行的 optimize local table t_word; 将会以 Online DDL 的方式执行

为了进一步验证此问题，查阅一下官方文档

• https://dev.mysql.com/doc/refman/5.7/en/temporary-files.html
• https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-space-requirements.html

由官方文档可知 Online DDL 会需要创建 临时日志文件， 临时排序文件，中间表文件，其中的 临时排序文件（Temporary sort files）会就写入 mysql的临时目录（tmpdir)。

查看下MySQL的 tmpdir 的位置

这里简单这几个文件做个小结

• 临时日志文件则由 innodb_sort_buffer_size 变量控制， 在（data-dir）中创建。
• 临时排序文件 则在 (tmp-dir) 中创建。
• 临时中间表文件则在（data-dir）中创建。

 解决方案

由上可知，解决方案只剩下这几种：

1. 方案一： 扩容系统盘，扩容系统盘后就会有足够的空间存储 Online DDL 所需的 临时排序文件。 （不建议）

* 虽然Linux支持在线扩容，但是依然存在风险，所以不建议。

2. 方案二： 设置 tmpdir 的目录位置到其他硬盘 （不建议）

网上普遍推荐这种，但是需要重启MySQL才能生效，代价太大，所以也不建议。

这里也备注一下修改方式 (若能接受重启MySQL，那么可以这样做)

Tips: 官方建议可以给 tmpdir 变量配置多个目录分摊负载 。

3. 方案三： 为 online ddl 单独设置 innodb_tmpdir ， 此变量允许动态设置，无需重启mysql即可生效。【推荐】

• innodb_tmpdir : 此选项在 MySQL 5.7.11 中引入，以帮助避免由于大型临时排序文件而可能发生的临时目录溢出

设置方式：

小结一下 ：

innodb_tmpdir ， 此变量允许动态设置，无需重启mysql即可生效。

• 有效值是 MySQL 数据目录路径（data-dir）以外的任何目录路径。
• 设置innodb_tmpdir要求用户有file权限
• 引入该innodb_tmpdir选项是为了帮助避免溢出位于tmpfs文件系统上的临时文件目录。ALTER TABLE由于在重建表的 联机操作期间创建的大型临时排序文件可能会发生此类溢出。
• 主从复制模式中，建议 innodb_tmpdir在每台服务器上单独配置。

3. 解决了上面的问题后，再执行DDL, 就会发现能执行成功了