MySQL 執行 Online DDL 操作報錯空間不足？

發表於2024-02-20
MySql

在 MySQL 中執行 Online DDL 之前，需要保證在三個方面的空間充足。

作者：徐文梁，愛可生 DBA 成員，一個執著於技術的資料庫工程師，主要負責資料庫日常運維工作。擅長 MySQL，Redis 及其他常見資料庫也有涉獵；喜歡垂釣，看書，看風景，結交新朋友。

愛可生開源社群出品，原創內容未經授權不得隨意使用，轉載請聯絡小編並註明來源。

本文約 1400 字，預計閱讀需要 4 分鐘。

問題背景

客戶反饋對某張表執行 alter table table_name engine=innodb; 時報錯空間不足。

透過登入資料庫檢視客戶的 tmpdir 設定的路徑，發現是 /tmp。該目錄磁碟空間本身較小，調整 tmpdir 的路徑與資料目錄相同，重新執行 ALTER 操作執行成功。

問題到此結束了，但是故事並沒有結束。透過檢視官網資訊，我們可以從這個小小的報錯中深挖更多資訊。

資訊解讀

從官網的論述中，我們可以瞭解到，在進行 Online DDL 操作時，需要保證以下三個方面的空間充足，否則可能會導致空間不足報錯。

臨時日誌檔案

當進行 Online DDL 操作建立索引或者更改表時，臨時日誌檔案會記錄期間的併發 DML 操作，臨時日誌檔案最大值由 innodb_online_alter_log_max_size 引數控制，如果 Online DDL 操作耗時較長（如果表資料量較大這是很有可能的），並且期間併發 DML 對錶中的記錄修改較多，則可能導致臨時日誌檔案大小超過 innodb_online_alter_log_max_size 值，從而引發 DB_ONLINE_LOG_TOO_BIG 錯誤，並回滾未提交的併發 DML 操作。

臨時排序檔案

對於會重建表的 Online DDL 操作，在建立索引期間，會將臨時排序檔案寫入到 MySQL 的臨時目錄。僅考慮 UNIX 系統，對應的引數為 _tmpdir_，如果 /tmp 目錄比較小，請設定該引數為其他目錄，否則可能會因為無法容納排序檔案而導致 Online DDL 失敗。

中間表檔案

對於會重建表的 Online DDL 操作，會在與原始表相同的目錄中建立一個臨時中間表檔案，中間表檔案可能需要與原始表大小相等的空間。中間表檔名以 #sql-ib 字首開頭，僅在 Online DDL 操作期間短暫出現。

前置準備

針對官網的論述，我們可以進行實際測試，這裡對臨時排序檔案和中間表檔案場景進行測試，為了故事更好的發展，先做一些準備工作：

1. 建立一個測試庫

資料目錄對應為 /opt/mysql/data/3310/my_test。

create database my_test;

2. 限制資料目錄大小

#建立一個指定大小的磁碟映象檔案，這裡為 600M
dd if=/dev/zero of=/root/test.img bs=60M count=10

#掛載裝置
losetup /dev/loop0 /root/test.img

#格式化裝置
mkfs.ext3 /dev/loop0

#掛載為資料夾，則限制其資料夾空間大小為 600M
mount -t ext3 /dev/loop0 /opt/mysql/data/3310/my_test

#修改屬組為 MySQL 服務對應使用者
chown -R mysql.mysql /opt/mysql/data/3310/my_test

3. 建立一張測試表

CREATE TABLE `student` (
`id` int(11) NOT NULL,
`name` varchar(50) NOT NULL,
`score` int(11) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`sex` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

4. 插入一些資料

注意：資料量不要太小，小於 /opt/mysql/data/3310/my_test 目錄的一半，建議 30% 左右。

./mysql_random_data_load -h127.0.0.1 -P3310 -uuniverse_op -p'xxx' --max-threads=8 my_test student 1500000

5. 修改 /tmp 大小

這裡 tmpdir 目錄為 /tmp，修改 /tmp 大小為一個較小值。

mount -o remount,size=1M tmpfs /tmp

6. 修改其他引數

修改 tmp_table_size 和 max_heap_table_size 值為較小值，這裡僅僅為了便於生成磁碟臨時檔案，生產環境不建議，會嚴重影響效能。

set sort_buffer_size=128*1024;
set tmp_table_size=128*1024;

場景測試

登入資料庫執行如下操作，可以觀察到新增索引失敗，報錯資訊如下：

mysql> alter table student add  idx_name index(name);
ERROR 1878 (HY000): Temporary file write failure.

執行如下操作修改 /tmp 目錄大小，再次執行 ALTER 操作成功。

[root@localhost ~]# mount -o remount,size=500M tmpfs /tmp
mysql> alter table student add index(name);
Query OK, 0 rows affected (4.92 sec)
Records: 0 Duplicates: 0 Warnings: 0

觀察 /opt/mysql/data/3310/my_test 目錄已使用空間，如果使用率較低，建議繼續插入資料到磁碟空間使用率超過 50%

執行如下操作，會報如下錯誤：

mysql> alter table student engine=innodb;
ERROR 1114 (HY000): The table 'student' is full

問題總結

好了，最後總結一下。為了我們的 Online DDL 操作順利進行，需要注意以下幾點：

  1. 在進行操作前，記得先檢查 innodb_online_alter_log_max_size 值，預估下是否需要修改。

    可以直接修改為一個較大值，但是沒有百分百的好事，壞處就是如果業務在 DDL 操作期間併發 DML 修改記錄較多，Online DDL 結束時鎖定表以應用記錄的 DML 時間會增加。所以，選擇好時機很重要，在對的時間做對的事，當然是在業務低峰期，或者考慮工具吧（pt-osc 或 ghost）。

  2. 在安裝例項時即設定 tmpdir 為合理的值。

    溫馨提示，該值不支援動態修改，真出現問題就晚了，畢竟生產上不允許隨便重啟服務的。

  3. 及時關注磁碟空間。

    不要等到磁碟空間快滿了才想著透過 Online DDL 操作進行碎片空間清理。例如 optimize table table_name;，alter table table_name engine=innodb; 等操作。這些操作本身也是需要額外的空間的，等待你的可能是 FAILURE。

相關文章