我們?cè)?Percona 支持欄目 經(jīng)常收到關(guān)于 MySQL 的 ibdata1 文件的這個(gè)問(wèn)題。

　　當(dāng)監(jiān)控服務(wù)器發(fā)送一個(gè)關(guān)于 MySQL 服務(wù)器存儲(chǔ)的報(bào)警時(shí)，恐慌就開(kāi)始了 —— 就是說(shuō)磁盤(pán)快要滿(mǎn)了。

　　一番調(diào)查后你意識(shí)到大多數(shù)地盤(pán)空間被 InnoDB 的共享表空間 ibdata1 使用。而你已經(jīng)啟用了 innodb file per_table ，所以問(wèn)題是：

　　ibdata1存了什么?

　　當(dāng)你啟用了 innodb_file_per_table ，表被存儲(chǔ)在他們自己的表空間里，但是共享表空間仍然在存儲(chǔ)其它的 InnoDB 內(nèi)部數(shù)據(jù)：

　　數(shù)據(jù)字典，也就是 InnoDB 表的元數(shù)據(jù)

　　變更緩沖區(qū)

　　雙寫(xiě)緩沖區(qū)

　　撤銷(xiāo)日志

　　其中的一些在 Percona 服務(wù)器 上可以被配置來(lái)避免增長(zhǎng)過(guò)大的。例如你可以通過(guò) innodb ibuf max_size 設(shè)置***變更緩沖區(qū)，或設(shè)置 innodbdoublewrite file 來(lái)將雙寫(xiě)緩沖區(qū)存儲(chǔ)到一個(gè)分離的文件。

　　MySQL 5.6 版中你也可以創(chuàng)建外部的撤銷(xiāo)表空間，所以它們可以放到自己的文件來(lái)替代存儲(chǔ)到 ibdata1。可以看看這個(gè) 文檔 。

　　什么引起 ibdata1 增長(zhǎng)迅速?

　　當(dāng) MySQL 出現(xiàn)問(wèn)題通常我們需要執(zhí)行的***個(gè)命令是：

SHOW ENGINE INNODB STATUS/G 

　　這將展示給我們一些很有價(jià)值的信息。我們從** TRANSACTION(事務(wù))**部分開(kāi)始檢查，然后我們會(huì)發(fā)現(xiàn)這個(gè)：

---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 

　　這是一個(gè)最常見(jiàn)的原因，一個(gè)14天前創(chuàng)建的相當(dāng)老的事務(wù)。這個(gè)狀態(tài)是 活動(dòng)的 ，這意味著 InnoDB 已經(jīng)創(chuàng)建了一個(gè)數(shù)據(jù)的快照，所以需要在撤銷(xiāo) 日志中維護(hù)舊頁(yè)面，以保障數(shù)據(jù)庫(kù)的一致性視圖，直到事務(wù)開(kāi)始。如果你的數(shù)據(jù)庫(kù)有大量的寫(xiě)入任務(wù)，那就意味著存儲(chǔ)了大量的撤銷(xiāo)頁(yè)。

　　如果你找不到任何長(zhǎng)時(shí)間運(yùn)行的事務(wù)，你也可以監(jiān)控INNODB STATUS 中的其他的變量，“ History list length(歷史記錄列表長(zhǎng)度) ”展示了一些等待清除操作。這種情況下問(wèn)題經(jīng)常發(fā)生，因?yàn)榍宄€程(或者老版本的主線程)不能像這些記錄進(jìn)來(lái)的速度一樣快地處理撤銷(xiāo)。

　　我怎么檢查什么被存儲(chǔ)到了 ibdata1 里了?

　　很不幸，MySQL 不提供查看什么被存儲(chǔ)到 ibdata1 共享表空間的信息，但是有兩個(gè)工具將會(huì)很有幫助。***個(gè)是馬克·卡拉漢制作的一個(gè)修改版 innochecksum ，它發(fā)布在 這個(gè)漏洞報(bào)告 里。

　　它相當(dāng)易于使用：

# ./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 個(gè)撤銷(xiāo)日志頁(yè)。 這占用了表空間的 93% 。

　　第二個(gè)檢查表空間內(nèi)容的方式是杰里米·科爾制作的 InnoDB Ruby 工具 。它是個(gè)檢查 InnoDB 的內(nèi)部結(jié)構(gòu)的更先進(jìn)的工具。例如我們可以使用 space-summary 參數(shù)來(lái)得到每個(gè)頁(yè)面及其數(shù)據(jù)類(lèi)型的列表。我們可以使用標(biāo)準(zhǔn)的 Unix 工具來(lái)統(tǒng)計(jì) 撤銷(xiāo)日志 頁(yè)的數(shù)量：

# innodb_space -f /var/lib/mysql/ibdata1 space-summary | grep UNDO_LOG | wc -l 
2 

　　盡管這種特殊的情況下，innochedcksum 更快更容易使用，但是我推薦你使用杰里米的工具去了解更多的 InnoDB 內(nèi)部的數(shù)據(jù)分布及其內(nèi)部結(jié)構(gòu)。

　　好，現(xiàn)在我們知道問(wèn)題所在了。下一個(gè)問(wèn)題：

　　我該怎么解決問(wèn)題?

　　這個(gè)問(wèn)題的答案很簡(jiǎn)單。如果你還能提交語(yǔ)句，就做吧。如果不能的話(huà)，你必須要?dú)⒌艟€程開(kāi)始回滾過(guò)程。那將停止 ibdata1 的增長(zhǎng)，但是很顯然，你的軟件會(huì)出現(xiàn)漏洞，有些人會(huì)遇到錯(cuò)誤。現(xiàn)在你知道如何去鑒定問(wèn)題所在，你需要使用你自己的調(diào)試工具或普通的查詢(xún)?nèi)罩緛?lái)找出誰(shuí)或者什么引起的問(wèn)題。

　　如果問(wèn)題發(fā)生在清除線程，解決方法通常是升級(jí)到新版本，新版中使用一個(gè)獨(dú)立的清除線程替代主線程。更多信息查看該 文檔

　　有什么方法回收已使用的空間么?

　　沒(méi)有，目前還沒(méi)有一個(gè)容易并且快速的方法。InnoDB 表空間從不收縮...參見(jiàn) 10 年之久的漏洞報(bào)告 ，***更新自詹姆斯·戴(謝謝)：

　　當(dāng)你刪除一些行，這個(gè)頁(yè)被標(biāo)為已刪除稍后重用，但是這個(gè)空間從不會(huì)被回收。唯一的方法是使用新的 ibdata1 啟動(dòng)數(shù)據(jù)庫(kù)。要做這個(gè)你應(yīng)該需要使用 mysqldump 做一個(gè)邏輯全備份，然后停止 MySQL 并刪除所有數(shù)據(jù)庫(kù)、ib_logfile 、ibdata1 文件。當(dāng)你再啟動(dòng) MySQL 的時(shí)候?qū)?huì)創(chuàng)建一個(gè)新的共享表空間。然后恢復(fù)邏輯備份。

　　總結(jié)

　　當(dāng) ibdata1 文件增長(zhǎng)太快，通常是 MySQL 里長(zhǎng)時(shí)間運(yùn)行的被遺忘的事務(wù)引起的。嘗試去解決問(wèn)題越快越好(提交或者殺死事務(wù))，因?yàn)椴唤?jīng)過(guò)痛苦緩慢的 mysqldump 過(guò)程，你就不能回收浪費(fèi)的磁盤(pán)空間。

　　也是非常推薦監(jiān)控?cái)?shù)據(jù)庫(kù)以避免這些問(wèn)題。我們的 MySQL 監(jiān)控插件 包括一個(gè) Nagios 腳本，如果發(fā)現(xiàn)了一個(gè)太老的運(yùn)行事務(wù)它可以提醒你。