Mysql?optimize?table?時(shí)報(bào)錯(cuò):Temporary?file?write?fail的解決

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

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

1. 問題描述

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

Tips： MySQL 8.x

# 執(zhí)行 optimize  命令時(shí)會發(fā)現(xiàn)報(bào)錯(cuò)
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)報(bào)錯(cuò) [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'

但是實(shí)際上我的 MySQL 的數(shù)據(jù)盤空間是足夠保存這個(gè)表的兩倍空間的，那么則說明這個(gè)報(bào)錯(cuò)（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)一步驗(yà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)建臨時(shí)日志文件，臨時(shí)排序文件，中間表文件，其中的臨時(shí)排序文件（Temporary sort files）會就寫入 mysql的臨時(shí)目錄（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 目錄實(shí)際上是在系統(tǒng)盤(vda1)上的。（這個(gè)是默認(rèn)位置）
> df -h /tmp
Filesystem      Size  Used Avail Use% Mounted on
/dev/vda1        20G  6.0G   13G  33% /

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

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

2. 解決方案

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

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

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

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

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

這里也備注一下修改方式 (若能接受重啟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 變量配置多個(gè)目錄分?jǐn)傌?fù)載。

3. 方案三：為 online ddl 單獨(dú)設(shè)置 innodb_tmpdir ，此變量允許動態(tài)設(shè)置，無需重啟mysql即可生效。【推薦】

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

設(shè)置方式：

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

# 如果報(bào)錯(cuò) ERROR 1231 (42000): Variable 'innodb_tmpdir' can't be set to the value of '/mnt/mysql-innodb-temp' 則說明這個(gè)目錄跟 data-dir 重復(fù)了,如果沒重復(fù)則執(zhí)行這個(gè)已經(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)依然報(bào)錯(cuò)，那么也可能是因?yà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選項(xiàng)是為了幫助避免溢出位于tmpfs文件系統(tǒng)上的臨時(shí)文件目錄。ALTER TABLE由于在重建表的聯(lián)機(jī)操作期間創(chuàng)建的大型臨時(shí)排序文件可能會發(fā)生此類溢出。
主從復(fù)制模式中，建議 innodb_tmpdir在每臺服務(wù)器上單獨(dú)配置。

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)

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

您可能感興趣的文章: