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

目录

• 1. 问题描述
• 2. 解决方案

1. 问题描述

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

Tips： MySQL 8.x

# 执行 optimize  命令时会发现报错
mysql>  optimize local table t_word;
+-------------------------+----------+----------+-------------------------------------------------------------------+
| Table                   | Op       | Msg_type | Msg_text                                                          |
+-------------------------+----------+----------+-------------------------------------------------------------------+
| abc.t_word | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| abc.t_word | optimize | error    | Temporary file write failure.                                     |
| abc.t_word | optimize | status   | Operation failed                                                  |
+-------------------------+----------+----------+-------------------------------------------------------------------+
3 rows in set, 1 warning (54 min 27.89 sec)

查看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 的位置

# 进入MySQL
mysql> SHOW VARIABLES LIKE 'tmpdir';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| tmpdir        | /tmp  |
+---------------+-------+
1 row in set (0.00 sec)

# 回到Linux ，可见 /tmp 目录实际上是在系统盘(vda1)上的。（这个是默认位置）
> df -h /tmp
Filesystem      Size  Used Avail Use% Mounted on
/dev/vda1        20G  6.0G   13G  33% /

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

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

2. 解决方案

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

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

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

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

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

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

#1.查看 tmpdir
mysql> SHOW VARIABLES LIKE 'tmpdir';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| tmpdir        | /tmp  |
+---------------+-------+
1 row in set (0.00 sec)

#2.创建 tmpdir目录

mkdir -p /data/tmpdir
chown -R mysql:mysql /data/tmpdir
chmod a+w /data/tmpdir

#3. 修改MySQL配置，设置 tmpdir
vim /etc/my.cnf
把tmpdir设置到 /data/tmpdir
tmpdir=/data/tmpdir

# 4. 修改完成后，重启mysql服务
service mysqld restart

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

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

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

设置方式：

	#注意： 执行前必须确保 /mnt/mysql-innodb-temp 目录已经创建了，并且给这个目录设置权限，确保MySQL能够读写改目录
 	mysql> set global innodb_tmpdir= '/mnt/mysql-innodb-temp'
 	```

下面是操作（踩坑）示例： 

```sh
# 注意： 必须先在系统上创建临时文件目录，并且确保mysql用户有权限访问这个目录
mysql> show VARIABLES like 'innodb_tmpdir';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| innodb_tmpdir |       |
+---------------+-------+
1 row in set (0.09 sec)

# 1. 设置   innodb_tmpdir
mysql> set global innodb_tmpdir= '/mnt/mysql-innodb-temp';
#  或者执行
mysql> set @@global.innodb_tmpdir=/mnt/mysql-innodb-temp

# 如果报错 ERROR 1231 (42000): Variable 'innodb_tmpdir' can't be set to the value of '/mnt/mysql-innodb-temp' 则说明这个目录跟 data-dir 重复了,如果没重复则执行这个已经查看详情
# 显示最近一次警告信息
# 语法： SHOW WARNINGS [LIMIT [offset,] row_count]
mysql> SHOW WARNINGS;
+---------+------+-------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                   |
+---------+------+-------------------------------------------------------------------------------------------+
| Warning | 1210 | InnoDB: Path doesn't exist.                                                               |
| Error   | 1231 | Variable 'innodb_tmpdir' can't be set to the value of '/mnt/mysql-innodb-temp' |
+---------+------+-------------------------------------------------------------------------------------------+

# Path doesn't exist. 说明该目录不存在, 创建目录后再执行命令，如果发现依然报错，那么也可能是因为没有文件权限
mysql>  SHOW WARNINGS limit 10;
+---------+------+---------------------------------------------------------------------------------+
| Level   | Code | Message                                                                         |
+---------+------+---------------------------------------------------------------------------------+
| Warning | 1210 | InnoDB: Server doesn't have permission in the given location.                   |
| Error   | 1231 | Variable 'innodb_tmpdir' can't be set to the value of '/mnt/mysql-innodb-temp' |
+---------+------+---------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

# 那么设置该目录的文件权限即可。
> chmod 777 /mnt/mysql-innodb-temp

# 再回到 mysql 发现执行成功了!
mysql> set @@global.innodb_tmpdir='/mnt/mysql-innodb-temp';
Query OK, 0 rows affected (0.00 sec)

# 检查下效果
mysql>  show VARIABLES like 'innodb_tmpdir';
+---------------+-----------------------------------+
| Variable_name | Value                             |
+---------------+-----------------------------------+
| innodb_tmpdir |/mnt/mysql-innodb-temp |
+---------------+-----------------------------------+
1 row in set (0.01 sec)

小结一下 ：

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

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

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

mysql> optimize local table t_word;
-------------------------+----------+----------+-------------------------------------------------------------------+
| Table                   | Op       | Msg_type | Msg_text                                                          |
+-------------------------+----------+----------+-------------------------------------------------------------------+
| abc.t_word| optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| abc.t_word| optimize | status   | OK                                                                |
+-------------------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (2 hours 29 min 54.20 sec)

以上为个人经验，希望能给大家一个参考，也希望大家多多支持我们。