作者：劉春雷
歡迎轉載，請註明作者出處，謝謝
如有問題，歡迎留言~

MySQL大表刪除解決方案

--20170619 劉春雷

一、表刪除問題彙總

(1)、mysql如何刪除表

(2)、刪除大表如何最佳化、解決

二、刪除表原理與最佳化

刪除表原理上分為2部分：

<1>buffer pool頁面清除過程。

在刪除表的時候，Innodb 會將檔案在buffer pool中對應的頁面清除。對於刪除表的頁面清除，只需要將頁面從flash佇列中刪除即可，而不需要去做flush操作，減小對系統的衝擊。

問題1：如果buffer pool很大，或者是在buffer pool中有很多需要被flush的頁面，那麼此時遍歷掃描頁面時就會佔用比較長的時間，導致其他事務在用到相應buffer pool例項時被阻塞，從而影響整個資料庫效能。

最佳化：涉及原始碼，最佳化困難

<2>刪除ibd磁碟檔案的過程。

問題1：表檔案過大，直接刪除會瞬時佔用大量IO，造成IO阻塞

最佳化：使用硬鏈

原理：一個磁碟上的檔案，可以由多個檔案系統的檔案引用，這多個檔案的完全相同的，都指向同一個磁碟上的檔案，當我們刪除任何一個檔案的時候，都不會影響真實的檔案，只是會將其引用資料減1，只有當被引用數目變為1的時候，再次刪除檔案，才會真正被刪除。刪除時，這兩種情況的區別很明顯，一個是在減少被引用數目，一個是真正做IO來刪除它

操作：

ln /data/mydata/my3306/testdb/table1.ibd /data/mydata/my3306/testdb/table1.ibd.hdlk

ls -lh /data/mydata/my3306/testdb 檢視檔案引用數(應該為2)

問題2：做完硬鏈，真正的大檔案刪除問題，直接rm 刪除，會造成IO瞬時高峰

最佳化：使用工具，多次少量的刪除

原理：利用系統檔案的truncate，指令碼工具為slowrm

三、slowrm

注：此工具作者為 “賈春生”，此處只分享~

3.1、需求

緩解大表刪除帶來的IO瞬間壓力

3.2、slowrm功能

<1>將大檔案按照指定速度進行刪除，降低IO瞬間壓力

3.3、slowrm實現

3.3.1、背景描述

先簡要介紹下slowrm工具開發的背景。

我們曾在《linux_mysql_DROP_TABLE操作步驟》中提到，對於大表的刪除，應當先建立硬鏈，drop table後，再刪除表資料檔案。

對於大表的資料檔案，可能會達到10G，也可以是100G級別，甚至更大。在linux下，這樣的大檔案在使用rm時，無疑會導致IO資源被強行佔用，表現為硬碟的io_util基本上是100%左右，會對其它IO操作造成阻塞。更可怕的是，rm單個檔案的過程是個原子過程，無法使用kill或kill -9來殺死rm程式，只能乖乖的等待它結束。

如果是在繁忙的線上服務所在的機器上做這樣的刪除操作，很可能會對線上服務產生影響。因此需要有一個平滑刪除大檔案的方案。而這樣的方案最終是與資料庫無關的，而是一個通用方案。

3.3.2、設計思路

對於刪除大檔案的操作，採用每次將檔案截斷一點，分多次截斷，最終直至檔案大小被截斷為0。

如下面的過程示例：

block_size = 1024576bytes;

while file_size >=block_size

begin

ftruncate (file, new_size);

(其中(old_size -new_size = block_size)

sleep 0.05 seconds;

end

ftruncate(file, 0);

因為既然rm一個檔案是不可中斷的，那麼我們就將刪除過程打散，分多次刪除一個，每次刪除一點。幸好現在的linux提供了支援該思路的API，truncate/ftruncate函式，可以對檔案截斷至任意長度()。經過測試，發現每次截斷一點，分多次截斷，最終直至檔案大小被截斷為0，這樣刪除大檔案需要更多的時間，但是對磁碟IO的影響卻大大減小了。因此採用該思路是可行的。

3.4、slowrm使用

slowrm [OPTIONS] FILES
對刪除檔案的方式與rm類似。

3.4.1、選項及引數

該工具的使用主要涉及兩個引數：

-b #size

每次需要截斷掉的檔案大小，單位為位元組，引數值需要是整數，預設值4096 * 256Bytes( = 1MB);

-s #seconds

每兩次截斷操作之間的時間間隔，單位為秒，引數值可以為小數。這兩個引數用以控制刪除速度，及刪除過程對磁碟IO的壓力。預設值0.1 second

-h, --help

檢視幫助資訊。

直接輸入slowrm，或輸入slowrm –h或slowrm --help可以檢視幫助。

3.4.2、刪除速度

實際上，可以看出，由#size * #seconds = #ioBPS Bytes/s，

即可計算得出每秒該操作消耗的IO吞吐量(位元組數)，也即大約每秒檔案所減小的位元組數。

可以看到預設刪除速度是: 4096 * 256Bytes / 0.1second= 10MB/s，被刪除的檔案每秒鐘可以減少10MB。

使用者可以透過上述兩個引數來控制刪除的速度以及對磁碟IO的壓力。

3.5、slowrm與rm對比

使用方式		slowrm	rm	備註
提示是否刪除		預設支援	rm -i	預設slowrm會詢問使用者是否刪除每一個指定檔案
刪除單個檔案		slowrm file	rm file	相同
刪除多個檔案	顯示指定檔案	slowrm file1 file2 file3	rm file1 file2 file3
刪除多個檔案	使用萬用字元	slowrm file*	rm file*
刪除硬鏈	硬鏈數大於1	刪除硬鏈同rm	刪除硬鏈	實際執行的就是unlink
刪除硬鏈	硬鏈數等於1	低速刪除檔案	全速刪除檔案	實質上在判斷硬鏈數上邏輯是一樣的，只是最後一個硬鏈的刪除方式不同
強制刪除檔案		slowrm -f slowrm --force	rm -f rm --force	相同
控制刪除速度	控制速度	slowrm -s 0.1 -b 262144 file (2MB/s)	不支援
控制刪除速度	預設速度	slowrm file 對應引數-s 0.1 -b 1048576(10MB/s)	不支援	一般使用預設引數即可
刪除其他型別檔案	目錄，軟鏈	不支援
遞迴刪除目錄		不支援	rm -rf 目錄

可見，一般情況下，還是使用rm刪除即可。

在需要低IO負載刪除大檔案時，可以使用slowrm。

slowrm對於rm是起到一個補充的作用。

3.6、注意

3.6.1、刪除檔案型別

目前，該工具只支援刪除具體常規檔案，無法對目錄進行遞迴刪除，但可以支援對多個檔案進行刪除，如slowrm *.logmysql.err.2014*

也無法對軟連結，檔案等進行刪除。

3.6.2、硬鏈的刪除

如果slowrm要刪除的檔案的硬連結數目大於1，那麼就會直接刪除該硬鏈；

如果待刪除的檔案硬鏈數目等於1，那麼會按照平滑刪除的方式，逐步截斷該檔案。

3.7、測試使用

製造大檔案

開始刪除

大小情況

壓力情況

Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await svctm %util

3.8、日常使用

1、開啟一個screen

screen -S slowrm_test

2、開始刪除檔案

slowrm file

或者指定速度，正常預設即可(10MB/s)

MySQL大表刪除解決方案