MySQL問(wèn)答系列之如何避免ibdata1文件大小暴漲

0、導(dǎo)讀

ibdata1文件是什么？

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

顯然這個(gè)文件會(huì)越來(lái)越大，innodb_autoextend_increment選項(xiàng)則指定了該文件每次自動(dòng)增長(zhǎng)的步進(jìn)，默認(rèn)是8M.

是什么原因?qū)е耰bdata1文件會(huì)越來(lái)越大？

ibdata1存放數(shù)據(jù)，索引和緩存等，是MYSQL的最主要的數(shù)據(jù)。所以隨著數(shù)據(jù)庫(kù)越來(lái)越大，表也會(huì)越大，這個(gè)無(wú)法避免的。如果時(shí)間長(zhǎng)了，越來(lái)越大，我們?cè)谔幚砣罩竞涂臻g的時(shí)候就不是那么方便了，就不知從何入手了。接下來(lái)我們就要處理下這樣的情況，分庫(kù)存儲(chǔ)數(shù)據(jù)。

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

1、問(wèn)題背景

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

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

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

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

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

innodb_data_file_path = ibdata1:1G:autoextend

當(dāng)然了，無(wú)論是否啟用了 innodb_file_per_table = 1，ibdata1文件都必須存在，因?yàn)樗仨毚鎯?chǔ)上述 InnoDB 引擎所依賴&必須的數(shù)據(jù)，尤其是上面加粗標(biāo)識(shí)的 rollback segments 和 undo space，它倆是引起 ibdata1 文件大小增加的最大原因，我們下面會(huì)詳細(xì)說(shuō)。

2、原因分析

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

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

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

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

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

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

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

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

3、解決方法建議

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

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

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

總結(jié)

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