一次mysql的.ibd檔案過大處理過程記錄

2022-06-29 18:04:10

一條zabbix微信的磁碟告警打破了往常的寧靜

收到告警之後發現是mysql的datadir目錄，按著平時習慣開始排查；過程就不說了，最後發現某個庫的目錄大小異常，然後進去檢視之後發現jdp_tb_trade.ibd過大，達到46G；跟真實資料量不符，就此打算對它下手處理。

那麼，我們知道ibd檔案是每個資料庫裡面每個表的資料空間，每個表的資料和索引都會存在自已的表空間中。

這麼重要的東西肯定不能直接線上上操作，畢竟之前完全不知道處理這個東西會產生什麼影響，那接下來就是測試環境的再現過程了：

測試環境：設定直接cp線上的my.cnf

然後建庫建表，插入資料，使該表的ibd檔案增大

最後如圖：

該檔案46G，表裡面的資料也有八百多萬條，接下來就是再現線上環境的操作了（線上環境增刪操作多），先刪個10資料，並且用優化命令對該表進行優化（optimize）：

但是發現在等待該命令執行結果的過程中，根目錄一直在增長：

直到跟目錄被佔用百分百之後，優化命令報錯了：

報錯之後跟目錄空間瞬間釋放了：

這裡我當時猜想到是因為臨時表的問題，但是不知道怎麼改臨時表的儲存目錄，那肯定是不懂就問。

問了DBA 大佬後，說是修改tmpdir引數即可（預設是指向tmp目錄）：

熟練的vim my.cnf

在[mysqld]下新增：

tmpdir = /ssd_data2/158mysql/107.sla

重啟mysql範例

在mysql命令符下檢視該引數目錄是否生效：

那就再執行一遍優化命令：

成功了，檔案也縮小了一個G。

接下來我又進一步測試，刪除表裡面資料，只保留10萬條資料；再執行optimize命令，並且觀察目錄佔用大小情況：

這裡值得一提的是：optimize命令執行時間只用了15分鐘，通過觀察目錄的變化發現臨時表大小大概在45G左右。

接下來是總結：

1）我原以為做一些小小的改動（只刪除了10條資料）會很快得到實驗的結果，結果可以在圖上面看到optimize命令執行了一個半小時；但是後面我再一次測試發現只用了15分鐘，可能是伺服器上其他業務影響了，時間上不好下結論。
這個命令會產生鎖表的效應，所以時間上需要注意。

2）學習知識點：

1、ibd檔案為何物，裡面是放什麼東西的：
上面也說到，是放表的後設資料，索引。

2、optimize這個命令的相關知識，會對資料庫造成什麼影響等：
已知有：
執行過程中會鎖表
會產生臨時表，佔用一定的空間
會影響主從延遲
（歡迎留言補充）

3、tmpdir這個引數：
臨時表指定存放目錄
可以跟innodb_tmpdir引數對比學習

4、這裡要提一個引數 “innodb_file_per_table=1”
組態檔裡最好把這個引數開啟，因為生產環境用的是innoDB的引擎，然後innodb會預設將所有庫的表資料都儲存在一個共用空間中：ibdata1，這樣不方便我們平時的優化。
該引數是讓每個表都會產生一個獨立的ibd檔案（也就是資料空間）

3）為什麼會產生這樣的事情呢？：

個人理解：平時我們刪除資料時，會使得表的ibd檔案產生空隙：也就是說，刪除資料之後它會傻傻的空在哪裡，如果沒有資料補進來就會一直空著；然後重複這增加，刪除一系列操作之後，該檔案隨著時間的推移變得越來越大。

目前我所知沒有特別好的辦法避免這一點，不過定時優化就好；

總結

到此這篇關於一次mysql的.ibd檔案過大處理過程的文章就介紹到這了,更多相關mysql .ibd檔案過大處理內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！

一次mysql的.ibd檔案過大處理過程記錄

熱門文章