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

收到告警之后發(fā)現(xiàn)是mysql的datadir目錄，按著平時習慣開始排查；過程就不說了，最后發(fā)現(xiàn)某個庫的目錄大小異常，然后進去查看之后發(fā)現(xiàn)jdp_tb_trade.ibd過大，達到46G；跟真實數(shù)據(jù)量不符，就此打算對它下手處理。

那么，我們知道ibd文件是每個數(shù)據(jù)庫里面每個表的數(shù)據(jù)空間，每個表的數(shù)據(jù)和索引都會存在自已的表空間中。

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

測試環(huán)境：配置直接cp線上的my.cnf

然后建庫建表，插入數(shù)據(jù)，使該表的ibd文件增大

最后如圖：

該文件46G，表里面的數(shù)據(jù)也有八百多萬條，接下來就是再現(xiàn)線上環(huán)境的操作了（線上環(huán)境增刪操作多），先刪個10數(shù)據(jù)，并且用優(yōu)化命令對該表進行優(yōu)化（optimize）：

但是發(fā)現(xiàn)在等待該命令執(zhí)行結果的過程中，根目錄一直在增長：

直到跟目錄被占用百分百之后，優(yōu)化命令報錯了：

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

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

問了DBA 大佬后，說是修改tmpdir參數(shù)即可（默認是指向tmp目錄）：

熟練的vim my.cnf

在[mysqld]下添加：

tmpdir = /ssd_data2/158mysql/107.sla

重啟mysql實例

在mysql命令符下查看該參數(shù)目錄是否生效：

那就再執(zhí)行一遍優(yōu)化命令：

成功了，文件也縮小了一個G。

接下來我又進一步測試，刪除表里面數(shù)據(jù)，只保留10萬條數(shù)據(jù)；再執(zhí)行optimize命令，并且觀察目錄占用大小情況：

這里值得一提的是：optimize命令執(zhí)行時間只用了15分鐘，通過觀察目錄的變化發(fā)現(xiàn)臨時表大小大概在45G左右。

接下來是總結：

1）我原以為做一些小小的改動（只刪除了10條數(shù)據(jù)）會很快得到實驗的結果，結果可以在圖上面看到optimize命令執(zhí)行了一個半小時；但是后面我再一次測試發(fā)現(xiàn)只用了15分鐘，可能是服務器上其他業(yè)務影響了，時間上不好下結論。
這個命令會產(chǎn)生鎖表的效應，所以時間上需要注意。

2）學習知識點：

1、ibd文件為何物，里面是放什么東西的：
上面也說到，是放表的元數(shù)據(jù)，索引。

2、optimize這個命令的相關知識，會對數(shù)據(jù)庫造成什么影響等：
已知有：
執(zhí)行過程中會鎖表
會產(chǎn)生臨時表，占用一定的空間
會影響主從延遲
（歡迎留言補充）

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

4、這里要提一個參數(shù) “innodb_file_per_table=1”
配置文件里最好把這個參數(shù)打開，因為生產(chǎn)環(huán)境用的是innoDB的引擎，然后innodb會默認將所有庫的表數(shù)據(jù)都存儲在一個共享空間中：ibdata1，這樣不方便我們平時的優(yōu)化。
該參數(shù)是讓每個表都會產(chǎn)生一個獨立的ibd文件（也就是數(shù)據(jù)空間）

3）為什么會產(chǎn)生這樣的事情呢？：

個人理解：平時我們刪除數(shù)據(jù)時，會使得表的ibd文件產(chǎn)生空隙：也就是說，刪除數(shù)據(jù)之后它會傻傻的空在哪里，如果沒有數(shù)據(jù)補進來就會一直空著；然后重復這增加，刪除一系列操作之后，該文件隨著時間的推移變得越來越大。

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

總結

到此這篇關于一次mysql的.ibd文件過大處理過程的文章就介紹到這了,更多相關mysql .ibd文件過大處理內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

最新評論