FAQ系列 | 如何避免ibdata1檔案大小暴漲

0、導讀

遇到InnoDB的共享表空間檔案ibdata1檔案大小暴增時，應該如何處理？

1、問題背景

用MySQL/InnoDB的童鞋可能也會有過煩惱，不知道為什麼原因，ibdata1檔案莫名其妙的增大，不知道該如何讓它縮回去，就跟30歲之後男人的肚腩一樣，汗啊，可喜可賀的是我的肚腩還沒長出來，hoho~

正式開始之前，我們要先知道ibdata1檔案是幹什麼用的。

ibdata1檔案是InnoDB存儲引擎的共享表空間檔案，該檔案中主要存儲着下面這些資料：

• data dictionary
• double write buffer
• insert buffer/change buffer
• rollback segments
• undo space
• Foreign key constraint system tables

另外，當選項 innodb_file_per_table = 0 時，在ibdata1檔案中還需要存儲 InnoDB 表資料&索引。ibdata1檔案從5.6.7版本開始，預設大小是12MB，而在這之前預設大小是10MB，其相關選項是 innodb_data_file_path，比如我一般是這麼設定的：

innodb_data_file_path = ibdata1:1G:autoextend

當然了，無論是否啟用了 innodb_file_per_table = 1，ibdata1檔案都必須存在，因為它必須存儲上述 InnoDB 引擎所依賴&必須的資料，尤其是上面加粗辨別的 rollback segments 和 undo space，它倆是引起 ibdata1 檔案大小增加的最大原因，我們下面會詳細說。

2、原因分析

我們知道，InnoDB是支援MVCC的，它和ORACLE類似，采用 undo log、redo log來實作MVCC特性的。在事務中對一行資料進行修改時，InnoDB 會把這行資料的舊版本資料存儲一份在undo log中，如果這時候有另一個事務又要修改這行資料，就又會把該事物最新可見的資料版本存儲一份在undo log中，以此類推，如果該資料目前有N個事務要對其進行修改，就需要存儲N份曆史版本（和ORACLE略有不同的是，InnoDB的undo log不完全是實體block，主要是邏輯日志，這個可以檢視 InnoDB 源碼或其他相關資料）。這些 undo log 需要等待該事務結束後，并再次根據事務隔離級别所決定的對其他事務而言的可見性進行判斷，确認是否可以将這些 undo log 删除掉，這個工作稱為 purge（purge 工作不僅僅是删除過期不用的 undo log，還有其他，以後有機會再說）。

那麼問題來了，如果目前有個事務中需要讀取到大量資料的曆史版本，而該事務因為某些原因無法今早送出或復原，而該事務發起之後又有大量事務需要對這些資料進行修改，這些新事務産生的 undo log 就一直無法被删除掉，形成了堆積，這就是導緻 ibdata1 檔案大小增大最主要的原因之一。這種情況最經典的場景就是大量資料備份，是以我們建議把備份工作放在專用的 slave server 上，不要放在 master server 上。

另一種情況是，InnoDB的 purge 工作因為本次 file i/o 性能是在太差或其他的原因，一直無法及時把可以删除的 undo log 進行purge 進而形成堆積，這是導緻 ibdata1 檔案大小增大另一個最主要的原因。這種場景發生在伺服器硬體配置比較弱，沒有及時跟上業務發展而更新的情況。

比較少見的一種是在早期運作在32位系統的MySQL版本中存在bug，當發現待 purge 的 undo log 總量超過某個值時，purge 線程直接放棄抵抗，再也不進行 purge 了，這個問題在我們早期使用32位MySQL 5.0版本時遇到的比較多，我們曾經遇到這個檔案漲到100多G的情況。後來我們費了很大功夫把這些執行個體都遷移到64位系統下，終于解決了這個問題。

最後一個是，選項 innodb_data_file_path 值一開始就沒調整或者設定很小，這就必不可免導緻 ibdata1 檔案增大了。Percona官方提供的 my.cnf 參考檔案中也一直沒把這個值加大，讓我百思不得其解，難道是為了像那個經常被我吐槽的xx那樣，故意留個暗門，好友善後續幫客戶進行優化嗎？（我心理太陰暗了，不好不好~~）

稍微總結下，導緻ibdata1檔案大小暴漲的原因有下面幾個：

• 有大量并發事務，産生大量的undo log；
• 有舊事務長時間未送出，産生大量舊undo log；
• file i/o性能差，purge進度慢；
• 初始化設定太小不夠用；
• 32-bit系統下有bug。

稍微題外話補充下，另一個熱門資料庫 PostgreSQL 的做法是把各個曆史版本的資料 和 原資料表空間 存儲在一起，是以不存在本案例的問題，也是以 PostgreSQL 的事務復原會非常快，并且還需要定期做 vaccum 工作（具體可參見PostgreSQL的MVCC實作機制，我可能說的不是完全正确哈）

3、解決方法建議

看到上面的這些問題原因描述，有些同學可能覺得這個好辦啊，對 ibdata1 檔案大小進行收縮，回收表空間不就結了嗎。悲劇的是，截止目前，InnoDB 還沒有辦法對 ibdata1 檔案表空間進行回收/收縮，一旦 ibdata1 檔案的肚子被搞大了，隻能把資料先備份後恢複再次重新初始化執行個體才能恢複原先的大小，或者把依次把各個獨立表空間檔案備份恢複到一個新執行個體中，除此外，沒什麼更好的辦法了。

當然了，這個問題也并不是不能防範，根據上面提到的原因，相應的建議對策是：

• 更新到5.6及以上（64-bit），采用獨立undo表空間，5.6版本開始就支援獨立的undo表空間了，再也不用擔心會把 ibdata1 檔案搞大；
• 初始化設定時，把 ibdata1 檔案至少設定為1GB以上；
• 增加purge線程數，比如設定 innodb_purge_threads = 8；
• 提高file i/o能力，該上SSD的趕緊上；
• 事務及時送出，不要積壓；
• 預設打開autocommit = 1，避免忘了某個事務長時間未送出；
• 檢查開發架構，确認是否設定了 autocommit=0，記得在事務結束後都有顯式送出或復原。