《Mysql實例MySQL InnoDB | 如何避免ibdata1文件暴漲》要點：
本文介紹了Mysql實例MySQL InnoDB | 如何避免ibdata1文件暴漲，希望對您有用。如果有疑問，可以聯系我們。
用MySQL/InnoDB的童鞋可能也會有過煩惱,不知道為什么原因,ibdata1文件莫名其妙的增大,不知道該如何讓它縮回去,就跟30歲之后男人的肚腩一樣,汗啊,可喜可賀的是我的肚腩還沒長出來,hoho~MYSQL應用
正式開始之前,我們要先知道ibdata1文件是干什么用的.MYSQL應用
ibdata1文件是InnoDB存儲引擎的共享表空間文件,該文件中主要存儲著下面這些數據：MYSQL應用
?MYSQL應用
另外,當選項 innodb_file_per_table = 0 時,在ibdata1文件中還需要存儲 InnoDB 表數據&索引.ibdata1文件從5.6.7版本開始,默認大小是12MB,而在這之前默認大小是10MB,其相關選項是 innodb_data_file_path,比如我一般是這么設置的：MYSQL應用
innodb_data_file_path = ibdata1:1G:autoextendMYSQL應用
當然了,無論是否啟用了 innodb_file_per_table = 1,ibdata1文件都必須存在,因為它必須存儲上述 InnoDB 引擎所依賴&必須的數據,尤其是上面加粗標識的 rollback segments 和 undo space,它倆是引起 ibdata1 文件大小增加的最大原因,我們下面會詳細說.MYSQL應用
我們知道,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,還有其他,以后有機會再說).MYSQL應用
那么問題來了,如果當前有個事務中需要讀取到大量數據的歷史版本,而該事務因為某些原因無法今早提交或回滾,而該事務發起之后又有大量事務需要對這些數據進行修改,這些新事務產生的 undo log 就一直無法被刪除掉,形成了堆積,這就是導致 ibdata1 文件大小增大最主要的原因之一.這種情況最經典的場景就是大量數據備份,因此我們建議把備份工作放在專用的 slave server 上,不要放在 master server 上.MYSQL應用
另一種情況是,InnoDB的 purge 工作因為本次 file i/o 性能是在太差或其他的原因,一直無法及時把可以刪除的 undo log 進行purge 從而形成堆積,這是導致 ibdata1 文件大小增大另一個最主要的原因.這種場景發生在服務器硬件配置比較弱,沒有及時跟上業務發展而升級的情況.MYSQL應用
比較少見的一種是在早期運行在32位系統的MySQL版本中存在bug,當發現待 purge 的 undo log 總量超過某個值時,purge 線程直接放棄抵抗,再也不進行 purge 了,這個問題在我們早期使用32位MySQL 5.0版本時遇到的比較多,我們曾經遇到這個文件漲到100多G的情況.后來我們費了很大功夫把這些實例都遷移到64位系統下,終于解決了這個問題.MYSQL應用
最后一個是,選項 innodb_data_file_path 值一開始就沒調整或者設置很小,這就必不可免導致 ibdata1 文件增大了.Percona官方提供的 my.cnf 參考文件中也一直沒把這個值加大,讓我百思不得其解,難道是為了像那個經常被我吐槽的xx那樣,故意留個暗門,好方便后續幫客戶進行優化嗎?(我心理太陰暗了,不好不好~~)MYSQL應用
稍微總結下,導致ibdata1文件大小暴漲的原因有下面幾個：MYSQL應用
有大量并發事務,產生大量的undo log；MYSQL應用
有舊事務長時間未提交,產生大量舊undo log；MYSQL應用
file i/o性能差,purge進度慢；MYSQL應用
初始化設置太小不夠用；MYSQL應用
32-bit系統下有bug.MYSQL應用
?MYSQL應用
稍微題外話補充下,另一個熱門數據庫 PostgreSQL 的做法是把各個歷史版本的數據 和 原數據表空間 存儲在一起,所以不存在本案例的問題,也因此 PostgreSQL 的事務回滾會非?？?并且還需要定期做 vaccum 工作(具體可參見PostgreSQL的MVCC實現機制,我可能說的不是完全正確哈)MYSQL應用
看到上面的這些問題原因描述,有些同學可能覺得這個好辦啊,對 ibdata1 文件大小進行收縮,回收表空間不就結了嗎.悲劇的是,截止目前,InnoDB 還沒有辦法對 ibdata1 文件表空間進行回收/收縮,一旦 ibdata1 文件的肚子被搞大了,只能把數據先備份后恢復再次重新初始化實例才能恢復原先的大小,或者把依次把各個獨立表空間文件備份恢復到一個新實例中,除此外,沒什么更好的辦法了.MYSQL應用
當然了,這個問題也并不是不能防范,根據上面提到的原因,相應的建議對策是：MYSQL應用
升級到5.6及以上(64-bit),采用獨立undo表空間,5.6版本開始就支持獨立的undo表空間了,再也不用擔心會把 ibdata1 文件搞大；MYSQL應用
初始化設置時,把 ibdata1 文件至少設置為1GB以上；MYSQL應用
增加purge線程數,比如設置 innodb_purge_threads = 8；MYSQL應用
提高file i/o能力,該上SSD的趕緊上；MYSQL應用
事務及時提交,不要積壓；MYSQL應用
默認打開autocommit = 1,避免忘了某個事務長時間未提交；MYSQL應用
檢查開發框架,確認是否設置了 autocommit=0,記得在事務結束后都有顯式提交或回滾.MYSQL應用
轉載請注明本頁網址：
http://www.snjht.com/jiaocheng/5717.html
