【MySQL】ibdata檔案增大的原因

背景
  早上和一個同事討論技術問題，談到ibdata檔案會隨著資料的使用而增大，而且在事務隔離級別RR 隔離級別下要比RC 隔離級別的大。本文嘗試分析兩個問題之一
 a ibdata 檔案隨著資料庫的使用而增大。
 b RR 隔離級別下比RC 隔離級別下增長的快大。
ibdata1 存放哪些資料？
表資料/索引 (innodb_file_per_table=0 時)
data dictionary aka metadata of InnoDB tables
undo 表空間 回滾段相關記錄
doublewrite buffer
change buffer
什麼原因導致ibdata 迅速增大呢？
從ibdata1檔案存放的內容來分析
我們都知道innodb的表有兩種存放方式：
當innodb_file_per_table=0時也即共享表空間方式,所有表的索引/資料統一存放在一個共享表空間中ibdata1檔案，隨著資料量的增大,共享表空間的檔案大小也迅速增長,同時空間回收困難；
當innodb_file_per_table=1時也即獨佔表空間方式,也就是一張表一個表空間(ibd檔案)，表中的索引和資料存放在獨立的表空間中,執行drop/truncate 操作可以快速的回收獨立表空間。
對於buffer 可以分別使用引數 innodb_doublewrite_file ，innodb_ibuf_max_size控制大小，他們帶來的空間增長可以忽略不計，最終 ibdata最終的大小取決於事務的大小/長短。
a 資料庫系統中存在長時間未提交的事務,或者在同一個事務中更新/插入很多資料，導致innodb需要維護很大的undo segments來保障一致性讀。
透過執行命令
SHOW ENGINE INNODB STATUSG
---TRANSACTION 36E, ACTIVE 1256288 sec
MySQL thread id 42, OS thread handle 0x7f8baaccc700, query id 7900290 localhost root
show engine innodb status
Trx read view will not see trx with id >= 36F, sees < 36F
例子中顯示有一個長達 1256288秒(14年)的事務未提交。active 意味著innodb在undo segment裡面建立資料的快照以便提供一致性讀。如果資料庫存在大量的長事務，就要耗費大量的undo segment。
b 5.5版本之前 purge undo thread是和master thread 共用的。存在大量長事務的時候導致purge undo的速度小於undo segment產生的速度。透過 innodb 引數 History list length

History list length  2308 表示有 2308個事務沒有清理，過大的值意味著purge thread 速度達到了瓶頸。5.5 版本開始MySQL 將purge thread 和master thread 分開，我們可以透過調整引數來加快purge undo的速度。
# yzsql 3311 param purge
如何檢視ibdata檔案中的內容呢？
MySQL 官方並沒有提供工具查ibata儲存了什麼內容，不過我們可以透過如下兩種工具
innochecksum，(感謝  Mark Callaghan)。
 ./innochecksum /var/lib/mysql/ibdata1

從上面的分析來看 undo log佔用了總ibdata1 的93%。
第二個工具： (  made by Jeremy Col)可以清晰地分析出ibdata1的組成（該工具需要bindata環境）
如何解決 ibdata1 不停的增大呢？ 
坦白的說我們沒有方法阻止其不停的增大，但是我們可以使用
1 規範開發同學的資料庫使用習慣，使用短小的事務替代大事務，並確保每個事務都有commit機制。
2 增加purge thread 數量，加快purge undo的速度儘快釋放undo空間。
3 升級到5.6 版本 獨立出undo 表空間來保持ibdata檔案在一個合理的大小。
當然我們也沒有優雅的辦法上增大的ibdata檔案縮小，這個檔案只增加不減小。

參考資料
[1] Reasons for run-away main Innodb Tablespace 
[2] Why is the ibdata1 file continuously growing in MySQL? 
[3]