[InnoDB] 要回刪除table資料後未被釋放的空間

最近發生公司的測試環境硬碟容量不足,導致MySQL寫入紀錄失敗。本以為是開發時不小心讓app server寫太多log導致,所以把年代久遠的log file清一清後就沒理它了,沒想到過幾天又出現硬碟容量不足的情況。繼續追查後發現原來MySQL也很肥大,於是也把一些不重要的免洗資料也砍一砍。

奇怪的是,砍完後硬碟使用率竟然沒有下降的跡象,原來這跟DB底層的運作有些關係。當我們下了DELETE FROM table WHERE ...語句後,DB其實只把這些紀錄的存檔註記刪除,並不會真的釋放這些空間還給系統,而是等待下次新增資料時,直接蓋掉舊的資料所佔的空間。

這樣做的好處是可以直接地節省硬碟I/O開銷,想想當我們刪除一筆記錄時,DB把空間還給系統,然後下一筆操作是個INSERT,DB又去把剛剛釋放的空間要回來,這樣在低速裝置一來一往的reclaim過程對要求高效的DB來說不值得。

可是面對今天只是測試環境,被分配到的硬體已經是低配了,我就是想要回那些資料被刪除的空間挪作他用怎麼辦呢。這時,可以執行以下語法:

OPTIMIZE TABLE my_huge_table;


這句操作會將table中現有的資料複製進新創的table中,接著將舊的table drop掉,最後再將新的table改名回舊的名稱。由於只搬未被刪除的資料進新的table,所以在硬碟上新table所佔的空間自然比舊的table還來得少,也就是那些被註記刪除的資料已經被我們還給系統了。

如果像我們公司一樣MySQL版本比較舊,當執行上面語句時DB出現了訊息説OPTIMIZE對InnoDB不管用,也可以使用以下第二種語句:

ALTER TABLE my_huge_table ENGINE='InnoDB';

這句跟第一句基本上是一樣的意思,就是在跟DB説我要更換table使用的引擎嘍,幫我從InnoDB換到InnoDB吧。而在這更換引擎的過程中,由於會重建table底層管理資料儲存的index,自然那些被註記刪除的空間就被還給系統了。

附上一張今天在測試環境中清掉那些未被DB釋放掉的空間,只單純optimize了幾張table就可以看到disk usage一個深深的plunge,真是神清氣爽呀。

P.S. 以上語句都只建議在自己的開發環境或測試環境執行,因為在重建的過程中是會鎖整張表的,正式環境經不起這樣的blocking。正式環境就乖乖放著讓DB自己未來在插入新資料時重新利用那些空間就好。


發佈留言