MySQL問答系列之如何避免ibdata1文件大小暴漲

0、導(dǎo)讀

ibdata1文件是什么？

ibdata1是一個用來構(gòu)建innodb系統(tǒng)表空間的文件，這個文件包含了innodb表的元數(shù)據(jù)、撤銷記錄、修改buffer和雙寫buffer。如果file-per-table選項打開的話，該文件則不一定包含所有表的數(shù)據(jù)。當(dāng)innodb_file_per_table選項打開的話，新創(chuàng)建表的數(shù)據(jù)和索引則不會存在系統(tǒng)表空間中，而是存放在各自表的.ibd文件中.

顯然這個文件會越來越大，innodb_autoextend_increment選項則指定了該文件每次自動增長的步進，默認(rèn)是8M.

是什么原因?qū)е耰bdata1文件會越來越大？

ibdata1存放數(shù)據(jù)，索引和緩存等，是MYSQL的最主要的數(shù)據(jù)。所以隨著數(shù)據(jù)庫越來越大，表也會越大，這個無法避免的。如果時間長了，越來越大，我們在處理日志和空間的時候就不是那么方便了，就不知從何入手了。接下來我們就要處理下這樣的情況，分庫存儲數(shù)據(jù)。

遇到InnoDB的共享表空間文件ibdata1文件大小暴增時，應(yīng)該如何處理？

1、問題背景

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

正式開始之前，我們要先知道ibdata1文件是干什么用的。

ibdata1文件是InnoDB存儲引擎的共享表空間文件，該文件中主要存儲著下面這些數(shù)據(jù)：

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

另外，當(dāng)選項 innodb_file_per_table = 0 時，在ibdata1文件中還需要存儲 InnoDB 表數(shù)據(jù)索引。ibdata1文件從5.6.7版本開始，默認(rèn)大小是12MB，而在這之前默認(rèn)大小是10MB，其相關(guān)選項是 innodb_data_file_path，比如我一般是這么設(shè)置的：

innodb_data_file_path = ibdata1:1G:autoextend

當(dāng)然了，無論是否啟用了 innodb_file_per_table = 1，ibdata1文件都必須存在，因為它必須存儲上述 InnoDB 引擎所依賴必須的數(shù)據(jù)，尤其是上面加粗標(biāo)識的 rollback segments 和 undo space，它倆是引起 ibdata1 文件大小增加的最大原因，我們下面會詳細(xì)說。

2、原因分析

我們知道，InnoDB是支持MVCC的，它和ORACLE類似，采用 undo log、redo log來實現(xiàn)MVCC特性的。在事務(wù)中對一行數(shù)據(jù)進行修改時，InnoDB 會把這行數(shù)據(jù)的舊版本數(shù)據(jù)存儲一份在undo log中，如果這時候有另一個事務(wù)又要修改這行數(shù)據(jù)，就又會把該事物最新可見的數(shù)據(jù)版本存儲一份在undo log中，以此類推，如果該數(shù)據(jù)當(dāng)前有N個事務(wù)要對其進行修改，就需要存儲N份歷史版本（和ORACLE略有不同的是，InnoDB的undo log不完全是物理block，主要是邏輯日志，這個可以查看 InnoDB 源碼或其他相關(guān)資料）。這些 undo log 需要等待該事務(wù)結(jié)束后，并再次根據(jù)事務(wù)隔離級別所決定的對其他事務(wù)而言的可見性進行判斷，確認(rèn)是否可以將這些 undo log 刪除掉，這個工作稱為 purge（purge 工作不僅僅是刪除過期不用的 undo log，還有其他，以后有機會再說）。

那么問題來了，如果當(dāng)前有個事務(wù)中需要讀取到大量數(shù)據(jù)的歷史版本，而該事務(wù)因為某些原因無法今早提交或回滾，而該事務(wù)發(fā)起之后又有大量事務(wù)需要對這些數(shù)據(jù)進行修改，這些新事務(wù)產(chǎn)生的 undo log 就一直無法被刪除掉，形成了堆積，這就是導(dǎo)致 ibdata1 文件大小增大最主要的原因之一。這種情況最經(jīng)典的場景就是大量數(shù)據(jù)備份，因此我們建議把備份工作放在專用的 slave server 上，不要放在 master server 上。

另一種情況是，InnoDB的 purge 工作因為本次 file i/o 性能是在太差或其他的原因，一直無法及時把可以刪除的 undo log 進行purge 從而形成堆積，這是導(dǎo)致 ibdata1 文件大小增大另一個最主要的原因。這種場景發(fā)生在服務(wù)器硬件配置比較弱，沒有及時跟上業(yè)務(wù)發(fā)展而升級的情況。

比較少見的一種是在早期運行在32位系統(tǒng)的MySQL版本中存在bug，當(dāng)發(fā)現(xiàn)待 purge 的 undo log 總量超過某個值時，purge 線程直接放棄抵抗，再也不進行 purge 了，這個問題在我們早期使用32位MySQL 5.0版本時遇到的比較多，我們曾經(jīng)遇到這個文件漲到100多G的情況。后來我們費了很大功夫把這些實例都遷移到64位系統(tǒng)下，終于解決了這個問題。

最后一個是，選項 innodb_data_file_path 值一開始就沒調(diào)整或者設(shè)置很小，這就必不可免導(dǎo)致 ibdata1 文件增大了。Percona官方提供的 my.cnf 參考文件中也一直沒把這個值加大，讓我百思不得其解，難道是為了像那個經(jīng)常被我吐槽的xx那樣，故意留個暗門，好方便后續(xù)幫客戶進行優(yōu)化嗎？（我心理太陰暗了，不好不好~~）

稍微總結(jié)下，導(dǎo)致ibdata1文件大小暴漲的原因有下面幾個：

• 有大量并發(fā)事務(wù)，產(chǎn)生大量的undo log；
• 有舊事務(wù)長時間未提交，產(chǎn)生大量舊undo log；
• file i/o性能差，purge進度慢；
• 初始化設(shè)置太小不夠用；
• 32-bit系統(tǒng)下有bug。

稍微題外話補充下，另一個熱門數(shù)據(jù)庫 PostgreSQL 的做法是把各個歷史版本的數(shù)據(jù) 和 原數(shù)據(jù)表空間 存儲在一起，所以不存在本案例的問題，也因此 PostgreSQL 的事務(wù)回滾會非?？?，并且還需要定期做 vaccum 工作（具體可參見PostgreSQL的MVCC實現(xiàn)機制，我可能說的不是完全正確哈）

3、解決方法建議

看到上面的這些問題原因描述，有些同學(xué)可能覺得這個好辦啊，對 ibdata1 文件大小進行收縮，回收表空間不就結(jié)了嗎。悲劇的是，截止目前，InnoDB 還沒有辦法對 ibdata1 文件表空間進行回收/收縮，一旦 ibdata1 文件的肚子被搞大了，只能把數(shù)據(jù)先備份后恢復(fù)再次重新初始化實例才能恢復(fù)原先的大小，或者把依次把各個獨立表空間文件備份恢復(fù)到一個新實例中，除此外，沒什么更好的辦法了。

當(dāng)然了，這個問題也并不是不能防范，根據(jù)上面提到的原因，相應(yīng)的建議對策是：

• 升級到5.6及以上（64-bit），采用獨立undo表空間，5.6版本開始就支持獨立的undo表空間了，再也不用擔(dān)心會把 ibdata1 文件搞大；
• 初始化設(shè)置時，把 ibdata1 文件至少設(shè)置為1GB以上；
• 增加purge線程數(shù)，比如設(shè)置 innodb_purge_threads = 8；
• 提高file i/o能力，該上SSD的趕緊上；
• 事務(wù)及時提交，不要積壓；
• 默認(rèn)打開autocommit = 1，避免忘了某個事務(wù)長時間未提交；
• 檢查開發(fā)框架，確認(rèn)是否設(shè)置了 autocommit=0，記得在事務(wù)結(jié)束后都有顯式提交或回滾。

總結(jié)

以上就是這篇文章的全部內(nèi)容了，希望本文的內(nèi)容對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，如果有疑問大家可以留言交流，謝謝大家對腳本之家的支持。