一條zabbix微信的磁盤告警打破了往常的寧?kù)o

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

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

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

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

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

最后如圖：

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

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

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

報(bào)錯(cuò)之后跟目錄空間瞬間釋放了：

這里我當(dāng)時(shí)猜想到是因?yàn)榕R時(shí)表的問(wèn)題，但是不知道怎么改臨時(shí)表的存儲(chǔ)目錄，那肯定是不懂就問(wèn)。

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

熟練的vim my.cnf

在[mysqld]下添加：

tmpdir = /ssd_data2/158mysql/107.sla

重啟mysql實(shí)例

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

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

成功了，文件也縮小了一個(gè)G。

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

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

接下來(lái)是總結(jié)：

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

2）學(xué)習(xí)知識(shí)點(diǎn)：

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

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

3、tmpdir這個(gè)參數(shù)：
臨時(shí)表指定存放目錄
可以跟innodb_tmpdir參數(shù)對(duì)比學(xué)習(xí)

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

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

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

目前我所知沒(méi)有特別好的辦法避免這一點(diǎn)，不過(guò)定時(shí)優(yōu)化就好；

總結(jié)

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

最新評(píng)論