服务器之家：专注于VPS、云服务器配置技术及软件下载分享
分类导航

Mysql|Sql Server|Oracle|Redis|MongoDB|PostgreSQL|Sqlite|DB2|mariadb|Access|数据库技术|

服务器之家 - 数据库 - Mysql - Mysql optimize table 时报错:Temporary file write fail的解决

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

2022-11-20 17:39大雪冬至 Mysql

这篇文章主要介绍了Mysql optimize table 时报错:Temporary file write fail的解决方案，具有很好的参考价值，希望对大家有所帮助。如有错误或未考虑完全的地方，望不吝赐教

1. 问题描述

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

Tips： MySQL 8.x

?
1
2
3
4
5
6
7
8
9
10
# 执行 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 的方式执行

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

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

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

查看下MySQL的 tmpdir 的位置

?
1
2
3
4
5
6
7
8
9
10
11
12
13
# 进入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 optimize table 时报错:Temporary file write fail的解决

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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
#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 中引入，以帮助避免由于大型临时排序文件而可能发生的临时目录溢出

设置方式：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
    #注意： 执行前必须确保 /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, 就会发现能执行成功了

?
1
2
3
4
5
6
7
8
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)

以上为个人经验，希望能给大家一个参考，也希望大家多多支持服务器之家。

原文链接：https://blog.csdn.net/UserFrank/article/details/124704420

  • mysql
  • table
  • optimize
  • temporary

    • 延伸 · 阅读

    精彩推荐
    • MysqlMySQL联合索引遵循最左前缀匹配原则

      MySQL联合索引遵循最左前缀匹配原则

      这篇文章主要介绍了MySQL联合索引遵循最左前缀匹配原则， MySQL联合索引遵循最左前缀匹配原则，即最左优先，查询的时候会优先匹配最左边的索引...

      ​​​​​​​一灯架构6692022-08-16
    • MysqlMySQL函数与存储过程字符串长度限制的解决

      MySQL函数与存储过程字符串长度限制的解决

      本文主要介绍了MySQL函数与存储过程字符串长度限制的解决，文中通过示例代码介绍的非常详细，对大家的学习或者工作具有一定的参考学习价值，需要的...

      Taysuesue6562022-08-15
    • MysqlMySQL 实现树的遍历详解及简单实现示例

      MySQL 实现树的遍历详解及简单实现示例

      这篇文章主要介绍了MySQL 实现树的遍历详解及简单实现示例的相关资料,这里提供了示例代码及测试结果，需要的朋友可以参考下...

      MYSQL教程网4572020-07-09
    • Mysqlmysql保存微信昵称特殊字符的方法

      mysql保存微信昵称特殊字符的方法

      我在用mysql 保存微信昵称，当插入昵称数据的时候，报错。接下来通过本文给大家介绍mysql保存微信昵称特殊字符的方法，需要的朋友一起看看吧...

      hejisan10582019-07-07
    • MysqlMySQL使用游标批量处理进行表操作

      MySQL使用游标批量处理进行表操作

      这篇文章主要介绍了MySQL使用游标批量进行表操作，包括批量添加索引、批量添加字段等，感兴趣的小伙伴们可以参考一下 ...

      pursuer.chen2662020-06-02
    • Mysqlmysql 8.0.22 安装配置图文教程

      mysql 8.0.22 安装配置图文教程

      这篇文章主要为大家详细介绍了mysql 8.0.22 安装配置图文教程，文中示例代码介绍的非常详细，具有一定的参考价值，感兴趣的小伙伴们可以参考一下...

      挣扎的工程师10152021-03-05
    • Mysql详解MySQL中的SQRT函数的使用方法

      详解MySQL中的SQRT函数的使用方法

      这篇文章主要介绍了详解MySQL中的SQRT函数的使用方法,是MySQL入门学习中的基础知识,需要的朋友可以参考下 ...

      MYSQL教程网3832020-05-08
    • MysqljQuery 绑定事件的方式总结

      jQuery 绑定事件的方式总结

      这篇文章主要介绍了jQuery 绑定事件的方式总结的相关资料,这里整理了几种方法，需要的朋友可以参考下...

      zzvip3442020-06-29
    最近更新
    编辑推荐
    阅读排行
    热门标签
    150 　 1044 　 1062 　 1053 　 1449 　 1045 　 1290 　 1819 　 2013 　 1406 　 1005 　 1864 　 1862 　 1146 　 1054 　 2006 　 1615 　 1215 　 1175 　 10048 　 2003 　 1366 　 28000 　 1577 　 1135 　 1093 　 1786 　 1067 　 1698 　 2002 　
    © 2019-2022 服务器之家(www.tuohang.net) 版权所有关于我们联系我们版权申明网站地图