MySQL大表刪除問題的解決方法

這篇文章主要講解了“MySQL大表刪除問題的解決方法”，文中的講解內(nèi)容簡單清晰，易于學(xué)習(xí)與理解，下面請大家跟著小編的思路慢慢深入，一起來研究和學(xué)習(xí)“MySQL大表刪除問題的解決方法”吧！

一、表刪除問題匯總

(1)、mysql如何刪除表

(2)、刪除大表如何優(yōu)化、解決

二、刪除表原理與優(yōu)化

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

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

在刪除表的時候，Innodb 會將文件在buffer pool中對應(yīng)的頁面清除。對于刪除表的頁面清除，只需要將頁面從flash隊列中刪除即可，而不需要去做flush操作，減小對系統(tǒng)的沖擊。

問題1：如果buffer pool很大，或者是在buffer pool中有很多需要被flush的頁面，那么此時遍歷掃描頁面時就會占用比較長的時間，導(dǎo)致其他事務(wù)在用到相應(yīng)buffer pool實例時被阻塞，從而影響整個數(shù)據(jù)庫性能。

優(yōu)化：涉及源碼，優(yōu)化困難

<2>刪除ibd磁盤文件的過程。

問題1：表文件過大，直接刪除會瞬時占用大量IO，造成IO阻塞

優(yōu)化：使用硬鏈

原理：一個磁盤上的文件，可以由多個文件系統(tǒng)的文件引用，這多個文件的完全相同的，都指向同一個磁盤上的文件，當(dāng)我們刪除任何一個文件的時候，都不會影響真實的文件，只是會將其引用數(shù)據(jù)減1，只有當(dāng)被引用數(shù)目變?yōu)?的時候，再次刪除文件，才會真正被刪除。刪除時，這兩種情況的區(qū)別很明顯，一個是在減少被引用數(shù)目，一個是真正做IO來刪除它

操作：

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

ls -lh    /data/mydata/my3306/testdb 查看文件引用數(shù)(應(yīng)該為2)

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

優(yōu)化：使用工具，多次少量的刪除

原理：利用系統(tǒng)文件的truncate，腳本工具為slowrm

三、slowrm

3.1、需求

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

3.2、slowrm功能

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

3.3、slowrm實現(xiàn)

3.3.1、背景描述

先簡要介紹下slowrm工具開發(fā)的背景。

我們曾在《linux_mysql_DROP_TABLE操作步驟》中提到，對于大表的刪除，應(yīng)當(dāng)先建立硬鏈，drop table后，再刪除表數(shù)據(jù)文件。

對于大表的數(shù)據(jù)文件，可能會達到10G，也可以是100G級別，甚至更大。在linux下，這樣的大文件在使用rm時，無疑會導(dǎo)致IO資源被強行占用，表現(xiàn)為硬盤的io_util基本上是100%左右，會對其它IO操作造成阻塞。更可怕的是，rm單個文件的過程是個原子過程，無法使用kill或kill -9來殺死rm進程，只能乖乖的等待它結(jié)束。

如果是在繁忙的線上服務(wù)所在的機器上做這樣的刪除操作，很可能會對線上服務(wù)產(chǎn)生影響。因此需要有一個平滑刪除大文件的方案。而這樣的方案最終是與數(shù)據(jù)庫無關(guān)的，而是一個通用方案。

3.3.2、設(shè)計思路

對于刪除大文件的操作，采用每次將文件截斷一點，分多次截斷，最終直至文件大小被截斷為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一個文件是不可中斷的，那么我們就將刪除過程打散，分多次刪除一個，每次刪除一點。幸好現(xiàn)在的linux提供了支持該思路的API，truncate/ftruncate函數(shù)，可以對文件截斷至任意長度()。經(jīng)過測試，發(fā)現(xiàn)每次截斷一點，分多次截斷，最終直至文件大小被截斷為0，這樣刪除大文件需要更多的時間，但是對磁盤IO的影響卻大大減小了。因此采用該思路是可行的。

3.4、slowrm使用

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

3.4.1、選項及參數(shù)

該工具的使用主要涉及兩個參數(shù)：

 -b   #size

每次需要截斷掉的文件大小，單位為字節(jié)，參數(shù)值需要是整數(shù)，默認(rèn)值4096 * 256Bytes( = 1MB);

 -s   #seconds

每兩次截斷操作之間的時間間隔，單位為秒，參數(shù)值可以為小數(shù)。這兩個參數(shù)用以控制刪除速度，及刪除過程對磁盤IO的壓力。默認(rèn)值0.1 second

 -h, --help

查看幫助信息。

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

3.4.2、刪除速度

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

即可計算得出每秒該操作消耗的IO吞吐量(字節(jié)數(shù))，也即大約每秒文件所減小的字節(jié)數(shù)。

    可以看到默認(rèn)刪除速度是: 4096 * 256Bytes / 0.1second= 10MB/s，被刪除的文件每秒鐘可以減少10MB。

用戶可以通過上述兩個參數(shù)來控制刪除的速度以及對磁盤IO的壓力。

3.5、slowrm與rm對比

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

在需要低IO負(fù)載刪除大文件時，可以使用slowrm。

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

3.6、注意

3.6.1、刪除文件類型

目前，該工具只支持刪除具體常規(guī)文件，無法對目錄進行遞歸刪除，但可以支持對多個文件進行刪除，如slowrm *.logmysql.err.2014*

也無法對軟鏈接，文件等進行刪除。

3.6.2、硬鏈的刪除

如果slowrm要刪除的文件的硬鏈接數(shù)目大于1，那么就會直接刪除該硬鏈；

如果待刪除的文件硬鏈數(shù)目等于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 

或者指定速度，正常默認(rèn)即可(10MB/s)

感謝各位的閱讀，以上就是“MySQL大表刪除問題的解決方法”的內(nèi)容了，經(jīng)過本文的學(xué)習(xí)后，相信大家對MySQL大表刪除問題的解決方法這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是億速云，小編將為大家推送更多相關(guān)知識點的文章，歡迎關(guān)注！