為什麼 MySQL 裡的 ibdata1 檔案不斷的增長？

我們在 Percona 支援欄目經常收到關於 MySQL 的 ibdata1 檔案的這個問題。

當監控伺服器傳送一個關於 MySQL 伺服器儲存的報警時，恐慌就開始了 —— 就是說磁碟快要滿了。

一番調查後你意識到大多數地盤空間被 InnoDB 的共享表空間 ibdata1 使用。而你已經啟用了 innodbfileper_table，所以問題是：

ibdata1存了什麼？

當你啟用了 innodb_file_per_table，表被儲存在他們自己的表空間裡，但是共享表空間仍然在儲存其它的 InnoDB 內部資料：

• 資料字典，也就是 InnoDB 表的後設資料
• 變更緩衝區
• 雙寫緩衝區
• 撤銷日誌

其中的一些在 Percona 伺服器上可以被配置來避免增長過大的。例如你可以通過 innodbibufmax_size 設定最大變更緩衝區，或設定 innodbdoublewritefile 來將雙寫緩衝區儲存到一個分離的檔案。

MySQL 5.6 版中你也可以建立外部的撤銷表空間，所以它們可以放到自己的檔案來替代儲存到 ibdata1。可以看看這個文件。

什麼引起 ibdata1 增長迅速？

當 MySQL 出現問題通常我們需要執行的第一個命令是：

SHOW ENGINE INNODB STATUS/G

這將展示給我們一些很有價值的資訊。我們從** TRANSACTION（事務）**部分開始檢查，然後我們會發現這個：

---TRANSACTION 36E, ACTIVE 1256288 sec
MySQL thread id 42, OS thread handle 0x7f8baaccc700, query id 7900290 localhost root
show engine innodb status
Trx read view will not see trx with id >= 36F, sees < 36F

這是一個最常見的原因，一個14天前建立的相當老的事務。這個狀態是活動的，這意味著 InnoDB 已經建立了一個資料的快照，所以需要在撤銷日誌中維護舊頁面，以保障資料庫的一致性檢視，直到事務開始。如果你的資料庫有大量的寫入任務，那就意味著儲存了大量的撤銷頁。

如果你找不到任何長時間執行的事務，你也可以監控INNODB STATUS 中的其他的變數，“History list length（歷史記錄列表長度）”展示了一些等待清除操作。這種情況下問題經常發生，因為清除執行緒（或者老版本的主執行緒）不能像這些記錄進來的速度一樣快地處理撤銷。

我怎麼檢查什麼被儲存到了 ibdata1 裡了？

很不幸，MySQL 不提供檢視什麼被儲存到 ibdata1 共享表空間的資訊，但是有兩個工具將會很有幫助。第一個是馬克·卡拉漢製作的一個修改版 innochecksum ，它釋出在這個漏洞報告裡。

它相當易於使用：

# ./innochecksum /var/lib/mysql/ibdata1
0 bad checksum
13 FIL_PAGE_INDEX
19272 FIL_PAGE_UNDO_LOG
230 FIL_PAGE_INODE
1 FIL_PAGE_IBUF_FREE_LIST
892 FIL_PAGE_TYPE_ALLOCATED
2 FIL_PAGE_IBUF_BITMAP
195 FIL_PAGE_TYPE_SYS
1 FIL_PAGE_TYPE_TRX_SYS
1 FIL_PAGE_TYPE_FSP_HDR
1 FIL_PAGE_TYPE_XDES
0 FIL_PAGE_TYPE_BLOB
0 FIL_PAGE_TYPE_ZBLOB
0 other
3 max index_id

全部的 20608 中有 19272 個撤銷日誌頁。這佔用了表空間的 93%。

第二個檢查表空間內容的方式是傑里米·科爾製作的 InnoDB Ruby 工具。它是個檢查 InnoDB 的內部結構的更先進的工具。例如我們可以使用 space-summary 引數來得到每個頁面及其資料型別的列表。我們可以使用標準的 Unix 工具來統計撤銷日誌頁的數量：

# innodb_space -f /var/lib/mysql/ibdata1 space-summary | grep UNDO_LOG | wc -l
19272

儘管這種特殊的情況下，innochedcksum 更快更容易使用，但是我推薦你使用傑里米的工具去了解更多的 InnoDB 內部的資料分佈及其內部結構。

好，現在我們知道問題所在了。下一個問題：

我該怎麼解決問題？

這個問題的答案很簡單。如果你還能提交語句，就做吧。如果不能的話，你必須要殺掉執行緒開始回滾過程。那將停止 ibdata1 的增長，但是很顯然，你的軟體會出現漏洞，有些人會遇到錯誤。現在你知道如何去鑑定問題所在，你需要使用你自己的除錯工具或普通的查詢日誌來找出誰或者什麼引起的問題。

如果問題發生在清除執行緒，解決方法通常是升級到新版本，新版中使用一個獨立的清除執行緒替代主執行緒。更多資訊檢視該文件

有什麼方法回收已使用的空間麼？

沒有，目前還沒有一個容易並且快速的方法。InnoDB 表空間從不收縮…參見10 年之久的漏洞報告，最新更新自詹姆斯·戴（謝謝）：

當你刪除一些行，這個頁被標為已刪除稍後重用，但是這個空間從不會被回收。唯一的方法是使用新的 ibdata1 啟動資料庫。要做這個你應該需要使用 mysqldump 做一個邏輯全備份，然後停止 MySQL 並刪除所有資料庫、ib_logfile、ibdata1 檔案。當你再啟動 MySQL 的時候將會建立一個新的共享表空間。然後恢復邏輯備份。

總結

當 ibdata1 檔案增長太快，通常是 MySQL 里長時間執行的被遺忘的事務引起的。嘗試去解決問題越快越好（提交或者殺死事務），因為不經過痛苦緩慢的 mysqldump 過程，你就不能回收浪費的磁碟空間。

也是非常推薦監控資料庫以避免這些問題。我們的 MySQL 監控外掛包括一個 Nagios 指令碼，如果發現了一個太老的執行事務它可以提醒你。