快捷導(dǎo)航

Mysql?optimize?table?時報錯:Temporary?file?write?fail的解決

更新時間：2022年09月27日 09:05:54 作者：大雪冬至

這篇文章主要介紹了Mysql?optimize?table?時報錯:Temporary?file?write?fail的解決方案，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教

1. 問題描述

當(dāng)我們執(zhí)行 optimize table xxx ; 嘗試進(jìn)行碎片整理時，也可能報錯 Temporary file write failure.

Tips： MySQL 8.x

# 執(zhí)行 optimize  命令時會發(fā)現(xiàn)報錯
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日志文件，也會發(fā)現(xiàn)報錯 [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 的數(shù)據(jù)盤空間是足夠保存這個表的兩倍空間的，那么則說明這個報錯（No space left on device）說的是系統(tǒng)盤的空間。

Tips: 由于在 MySQL 5.6.7 之后推出了 Online DDL ，所以我執(zhí)行的 optimize local table t_word; 將會以 Online DDL 的方式執(zhí)行

為了進(jìn)一步驗證此問題，查閱一下官方文檔

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 會需要創(chuàng)建臨時日志文件，臨時排序文件，中間表文件，其中的臨時排序文件（Temporary sort files）會就寫入 mysql的臨時目錄（tmpdir)。

查看下MySQL的 tmpdir 的位置

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

# 回到Linux ，可見 /tmp 目錄實際上是在系統(tǒng)盤(vda1)上的。（這個是默認(rèn)位置）
> df -h /tmp
Filesystem      Size  Used Avail Use% Mounted on
/dev/vda1        20G  6.0G   13G  33% /

這里簡單這幾個文件做個小結(jié)

臨時日志文件則由 innodb_sort_buffer_size 變量控制，在（data-dir）中創(chuàng)建。
臨時排序文件則在 (tmp-dir) 中創(chuàng)建。
臨時中間表文件則在（data-dir）中創(chuàng)建。

2. 解決方案

由上可知，解決方案只剩下這幾種：

1. 方案一：擴容系統(tǒng)盤，擴容系統(tǒng)盤后就會有足夠的空間存儲 Online DDL 所需的臨時排序文件。（不建議）

* 雖然Linux支持在線擴容，但是依然存在風(fēng)險，所以不建議。

2. 方案二：設(shè)置 tmpdir 的目錄位置到其他硬盤（不建議）

網(wǎng)上普遍推薦這種，但是需要重啟MySQL才能生效，代價太大，所以也不建議。

這里也備注一下修改方式 (若能接受重啟MySQL，那么可以這樣做)

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

#2.創(chuàng)建 tmpdir目錄

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

#3. 修改MySQL配置，設(shè)置 tmpdir 
vim /etc/my.cnf
把tmpdir設(shè)置到 /data/tmpdir
tmpdir=/data/tmpdir

# 4. 修改完成后，重啟mysql服務(wù)
service mysqld restart

Tips: 官方建議可以給 tmpdir 變量配置多個目錄分?jǐn)傌?fù)載。

3. 方案三：為 online ddl 單獨設(shè)置 innodb_tmpdir ，此變量允許動態(tài)設(shè)置，無需重啟mysql即可生效?！就扑]】

innodb_tmpdir : 此選項在 MySQL 5.7.11 中引入，以幫助避免由于大型臨時排序文件而可能發(fā)生的臨時目錄溢出

設(shè)置方式：

	#注意： 執(zhí)行前必須確保 /mnt/mysql-innodb-temp 目錄已經(jīng)創(chuàng)建了，并且給這個目錄設(shè)置權(quán)限，確保MySQL能夠讀寫改目錄
 	mysql> set global innodb_tmpdir= '/mnt/mysql-innodb-temp'
 	```
	
下面是操作（踩坑）示例： 
	
```sh
# 注意： 必須先在系統(tǒng)上創(chuàng)建臨時文件目錄，并且確保mysql用戶有權(quán)限訪問這個目錄
mysql> show VARIABLES like 'innodb_tmpdir';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| innodb_tmpdir |       |
+---------------+-------+
1 row in set (0.09 sec)

# 1. 設(shè)置   innodb_tmpdir
mysql> set global innodb_tmpdir= '/mnt/mysql-innodb-temp';
#  或者執(zhí)行
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 重復(fù)了,如果沒重復(fù)則執(zhí)行這個已經(jīng)查看詳情
# 顯示最近一次警告信息
# 語法： 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. 說明該目錄不存在, 創(chuàng)建目錄后再執(zhí)行命令，如果發(fā)現(xiàn)依然報錯，那么也可能是因為沒有文件權(quán)限
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)

# 那么設(shè)置該目錄的文件權(quán)限即可。 
> chmod 777 /mnt/mysql-innodb-temp

# 再回到 mysql 發(fā)現(xiàn)執(zhí)行成功了!
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)

小結(jié)一下：

innodb_tmpdir ，此變量允許動態(tài)設(shè)置，無需重啟mysql即可生效。

有效值是 MySQL 數(shù)據(jù)目錄路徑（data-dir）以外的任何目錄路徑。
設(shè)置innodb_tmpdir要求用戶有file權(quán)限
引入該innodb_tmpdir選項是為了幫助避免溢出位于tmpfs文件系統(tǒng)上的臨時文件目錄。ALTER TABLE由于在重建表的聯(lián)機操作期間創(chuàng)建的大型臨時排序文件可能會發(fā)生此類溢出。
主從復(fù)制模式中，建議 innodb_tmpdir在每臺服務(wù)器上單獨配置。

3. 解決了上面的問題后，再執(zhí)行DDL, 就會發(fā)現(xiàn)能執(zhí)行成功了

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)

以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。

您可能感興趣的文章: