為什么mysql里的ibdata1文件不斷的增長？
我們在 Percona 支持欄目 經常收到關于 MySQL 的 ibdata1 文件的這個問題。
當監控服務器發送一個關于 MySQL 服務器存儲的報警時，恐慌就開始了 —— 就是說磁盤快要滿了。
一番調查后你意識到大多數地盤空間被 InnoDB 的共享表空間 ibdata1 使用。而你已經啟用了 innodb file per_table ，所以問題是：
ibdata1存了什么?
當你啟用了 innodb_file_per_table ，表被存儲在他們自己的表空間里，但是共享表空間仍然在存儲其它的 InnoDB 內部數據：
數據字典，也就是 InnoDB 表的元數據
變更緩沖區
雙寫緩沖區
撤銷日志
其中的一些在 Percona 服務器 上可以被配置來避免增長過大的。例如你可以通過 innodb ibuf max_size 設置***變更緩沖區，或設置 innodbdoublewrite file 來將雙寫緩沖區存儲到一個分離的文件。
MySQL 5.6 版中你也可以創建外部的撤銷表空間，所以它們可以放到自己的文件來替代存儲到 ibdata1。可以看看這個 文檔 。
什么引起 ibdata1 增長迅速?
當 MySQL 出現問題通常我們需要執行的***個命令是：
- SHOW ENGINE INNODB STATUS/G
這將展示給我們一些很有價值的信息。我們從** TRANSACTION(事務)**部分開始檢查，然后我們會發現這個：
- ---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
這是一個最常見的原因，一個14天前創建的相當老的事務。這個狀態是 活動的 ，這意味著 InnoDB 已經創建了一個數據的快照，所以需要在撤銷 日志中維護舊頁面，以保障數據庫的一致性視圖，直到事務開始。如果你的數據庫有大量的寫入任務，那就意味著存儲了大量的撤銷頁。
如果你找不到任何長時間運行的事務，你也可以監控INNODB STATUS 中的其他的變量，“ History list length(歷史記錄列表長度) ”展示了一些等待清除操作。這種情況下問題經常發生，因為清除線程(或者老版本的主線程)不能像這些記錄進來的速度一樣快地處理撤銷。
我怎么檢查什么被存儲到了 ibdata1 里了?
很不幸，MySQL 不提供查看什么被存儲到 ibdata1 共享表空間的信息，但是有兩個工具將會很有幫助。***個是馬克·卡拉漢制作的一個修改版 innochecksum ，它發布在 這個漏洞報告 里。
它相當易于使用：
- # ./innochecksum /var/lib/mysql/ibdata1
- bad checksum
- FIL_PAGE_INDEX
- 2 FIL_PAGE_UNDO_LOG
- FIL_PAGE_INODE
- FIL_PAGE_IBUF_FREE_LIST
- FIL_PAGE_TYPE_ALLOCATED
- FIL_PAGE_IBUF_BITMAP
- FIL_PAGE_TYPE_SYS
- FIL_PAGE_TYPE_TRX_SYS
- FIL_PAGE_TYPE_FSP_HDR
- FIL_PAGE_TYPE_XDES
- FIL_PAGE_TYPE_BLOB
- FIL_PAGE_TYPE_ZBLOB
- other
- max index_id
全部的 20608 中有 19272 個撤銷日志頁。 這占用了表空間的 93% 。
第二個檢查表空間內容的方式是杰里米·科爾制作的 InnoDB Ruby 工具 。它是個檢查 InnoDB 的內部結構的更先進的工具。例如我們可以使用 space-summary 參數來得到每個頁面及其數據類型的列表。我們可以使用標準的 Unix 工具來統計 撤銷日志 頁的數量：
- # innodb_space -f /var/lib/mysql/ibdata1 space-summary | grep UNDO_LOG | wc -l
- 2
盡管這種特殊的情況下，innochedcksum 更快更容易使用，但是我推薦你使用杰里米的工具去了解更多的 InnoDB 內部的數據分布及其內部結構。
好，現在我們知道問題所在了。下一個問題：
我該怎么解決問題?
這個問題的答案很簡單。如果你還能提交語句，就做吧。如果不能的話，你必須要殺掉線程開始回滾過程。那將停止 ibdata1 的增長，但是很顯然，你的軟件會出現漏洞，有些人會遇到錯誤。現在你知道如何去鑒定問題所在，你需要使用你自己的調試工具或普通的查詢日志來找出誰或者什么引起的問題。
如果問題發生在清除線程，解決方法通常是升級到新版本，新版中使用一個獨立的清除線程替代主線程。更多信息查看該 文檔
有什么方法回收已使用的空間么?
沒有，目前還沒有一個容易并且快速的方法。InnoDB 表空間從不收縮...參見 10 年之久的漏洞報告 ，***更新自詹姆斯·戴(謝謝)：
當你刪除一些行，這個頁被標為已刪除稍后重用，但是這個空間從不會被回收。唯一的方法是使用新的 ibdata1 啟動數據庫。要做這個你應該需要使用 mysqldump 做一個邏輯全備份，然后停止 MySQL 并刪除所有數據庫、ib_logfile 、ibdata1 文件。當你再啟動 MySQL 的時候將會創建一個新的共享表空間。然后恢復邏輯備份。
總結
當 ibdata1 文件增長太快，通常是 MySQL 里長時間運行的被遺忘的事務引起的。嘗試去解決問題越快越好(提交或者殺死事務)，因為不經過痛苦緩慢的 mysqldump 過程，你就不能回收浪費的磁盤空間。
也是非常推薦監控數據庫以避免這些問題。我們的 MySQL 監控插件 包括一個 Nagios 腳本，如果發現了一個太老的運行事務它可以提醒你。
