一次mysql的.ibd文件過大處理過程記錄

Posted on by

一條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文件過大處理內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：