mysql 裡的 ibdata1 檔案不斷的增長？

標籤：

我們在 Percona 支援欄目經常收到關於 MySQL 的 ibdata1 檔案的這個問題。當監控伺服器發送一個關於 MySQL 伺服器儲存的警示時，恐慌就開始了 —— 就是說磁碟快要滿了。一番調查後你意識到大多數地盤空間被 InnoDB 的共用資料表空間 ibdata1 使用。而你已經啟用了 innodb_file_per_table，所以問題是：

ibdata1存了什嗎？

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

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

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

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

什麼引起 ibdata1 增長迅速？

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

 

1	SHOW ENGINE INNODB STATUS/G

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

 

1234	---TRANSACTION 36E, ACTIVE 1256288 secMySQL thread id 42, OS thread handle 0x7f8baaccc700, query id 7900290 localhost rootshow engine innodb statusTrx read view will not see trx with id >= 36F, sees < 36F

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

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

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

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

它相當便於使用：

 

12345678910111213141516

# ./innochecksum /var/lib/mysql/ibdata10 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 工具來統計撤銷日誌頁的數量：

 

12	# innodb_space -f /var/lib/mysql/ibdata1 space-summary | grep UNDO_LOG | wc -l19272

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

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

我該怎麼解決問題？

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

如果問題發生在清除線程，解決方案通常是升級到新版本，新版中使用一個獨立的清除線程替代主線程。更多資訊查看該文檔

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

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

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

總結

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

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

mysql 裡的 ibdata1 檔案不斷的增長？