MySQL 臨時目錄遷移至記憶體 (tmpfs) 實戰：消除磁碟 I/O 瓶頸大幅提升臨時表效能

MySQL 臨時目錄遷移至記憶體 (tmpfs) 實戰

MySQL 執行複雜查詢 (GROUP BY、ORDER BY、JOIN) 時會建立臨時表。若臨時表超過 tmp_table_size/max_heap_table_size 或包含 TEXT/BLOB 欄位，MySQL 會將其轉為磁碟 MyISAM 表，導致大量磁碟 I/O。將 tmpdir 掛載至記憶體檔案系統 tmpfs，可徹底消除此瓶頸。

現狀診斷

mysql> SHOW GLOBAL STATUS LIKE 'Created_tmp%tables';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Created_tmp_disk_tables | 49094    |  -- 磁碟臨時表數 (需關注)
| Created_tmp_tables      | 37842181 |  -- 總臨時表數
+-------------------------+----------+

mysql> SHOW GLOBAL VARIABLES LIKE '%table_size';
+---------------------+----------+
| Variable_name       | Value    |
+---------------------+----------+
| max_heap_table_size | 25165824 |  -- 24MB
| tmp_table_size      | 25165824 |  -- 24MB
+---------------------+----------+

mysql> SHOW GLOBAL VARIABLES LIKE 'tmpdir';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| tmpdir        | /tmp  |  -- 預設磁碟目錄
+---------------+-------+

方案：建立 tmpfs 掛載點並修改 tmpdir

1. 建立掛載目錄並設定權限

mkdir -p /mnt/ramdisk
chown mysql:mysql /mnt/ramdisk

2. 掛載 tmpfs (大小視記憶體而定，建議 1-4GB)

mount -t tmpfs -o size=2G tmpfs /mnt/ramdisk

3. 設定開機自動掛載 (/etc/fstab)

tmpfs /mnt/ramdisk tmpfs rw,mode=1777,size=2G 0 0

4. 修改 MySQL 配置 (my.cnf)

[mysqld]
tmpdir = /mnt/ramdisk

5. 重啟 MySQL 生效

systemctl restart mysqld

驗證成效

mysql> SHOW GLOBAL VARIABLES LIKE 'tmpdir';
+---------------+--------------+
| Variable_name | Value        |
+---------------+--------------+
| tmpdir        | /mnt/ramdisk |
+---------------+--------------+

運行高負載查詢後，觀察 Created_tmp_disk_tables 不再增長，查詢延遲顯著下降。

關鍵原理與避坑

• tmpfs vs ramfs： tmpfs 支援大小限制、交換分區溢寫、權限模式，生產環境**強烈推薦 tmpfs**；ramfs 無大小限制，極易耗盡記憶體導致 OOM Killer 殺進程。
• 大小設定： size=2G 為上限，實際佔用隨寫入動態增長；需預留足夠記憶體給 InnoDB Buffer Pool 與 OS。
• TEXT/BLOB 限制： 即使用 tmpfs，含 TEXT/BLOB 的臨時表仍會用磁碟 MyISAM (MEMORY 引擎不支援)。根治需調整查詢避免大欄位參與排序/分組，或升級 MySQL 8.0+ 利用 TempTable 引擎支援 VARBINARY/VARCHAR。
• 持久化風險： tmpfs 為易失性儲存，**重啟資料消失**，但 MySQL 臨時表本身即短暫，完全符合語義。

效能提升預期： 臨時表密集型負載 (大報表、ETL、複雜 JOIN) 可見 **2-10x** 查詢加速，磁碟 IOPS 壓力近乎歸零。