幾種常見的 MySQL/PolarDB-MySQL 回收表空間方法對(duì)比

2022-12-14 10:16:45

架構(gòu)師和DBA在系統(tǒng)設(shè)計(jì)之初就要考慮滿足未來(lái)幾年內(nèi)的業(yè)務(wù)需求，同時(shí)又能最大化地節(jié)省成本，這是比較合理的架構(gòu)布局和容量規(guī)劃的方法。

背景

為什么需要回收表空間？任何一個(gè)存儲(chǔ)或您購(gòu)買的實(shí)例規(guī)格都有容量限制，并且根據(jù)存儲(chǔ)介質(zhì)不同，保存方式不同，相應(yīng)地成本也會(huì)不同。在線數(shù)據(jù)庫(kù)的存儲(chǔ)成本是比較高的，所以架構(gòu)師和DBA在系統(tǒng)設(shè)計(jì)之初就要考慮滿足未來(lái)幾年內(nèi)的業(yè)務(wù)需求，同時(shí)又能最大化地節(jié)省成本，這是比較合理的架構(gòu)布局和容量規(guī)劃的方法。而大多數(shù)系統(tǒng)是沒有經(jīng)過以上步驟直接上線的，這種隨著業(yè)務(wù)的發(fā)展在線數(shù)據(jù)會(huì)保留的越來(lái)越多，當(dāng)存儲(chǔ)容量不夠時(shí)可以通過升級(jí)實(shí)例規(guī)格或硬件解決，但如果沒有更大的規(guī)格時(shí)，只能刪除數(shù)據(jù)回收表空間了。

回收表空間的常見方法

在刪除回收表空間時(shí)，通常有以下幾種方法：

編號(hào)	刪除方法	回收方法	適合場(chǎng)景	弊
1	CREATE TABLE A' LIKE A; INSERT INTO A' SELECT * FROM A WHERE ; DROP TABLE A; RENAMETABLE A' TO A;	DROP TABLE A;	保留數(shù)據(jù)少，刪除數(shù)據(jù)多；但要極短時(shí)間暫停源表上的數(shù)據(jù)寫入（通常毫秒級(jí)別完成）；	可能會(huì)引起性能抖動(dòng)
2	DELETE * FROM A WHERE ; ALTER TABLE A ENGINE=INNODB;/OPTIMIZE TABLE A;	ALTER TABLE A ENGINE=INNODB;/OPTIMIZE TABLE A;	保留數(shù)據(jù)多，刪除數(shù)據(jù)少；建議DELETE時(shí)用DMS的無(wú)鎖數(shù)據(jù)變更(參考 ?https://help.aliyun.com/document_detail/162507.html)，否則DELETE時(shí)也可能引起性能抖動(dòng)	可能會(huì)引起性能抖動(dòng)
3	ALTER TABLE A DROP PARTITION partition_name;	ALTER TABLE A DROP PARTITION partition_name;	分區(qū)表	可能會(huì)引起性能抖動(dòng)
4	DROP TABLE A_0000/A_20100101;	DROP TABLE A_0000/A_20100101;	已經(jīng)人為分表存儲(chǔ)設(shè)置,如：按取模或日期分表	可能會(huì)引起性能抖動(dòng)

針對(duì)DROP TABLE A可能會(huì)帶來(lái)的性能抖動(dòng)可以通過阿里云內(nèi)核經(jīng)過特殊優(yōu)化Purge Large File Asynchronously(https://help.aliyun.com/document_detail/134095.html)默認(rèn)已經(jīng)打開。

而對(duì)于ALTER TABLE的操作，目前業(yè)界開源的有g(shù)h-ost、pt-online-schema-change和OnlineSchemaChange，阿里云RDS MySQL也專門研發(fā)了無(wú)鎖結(jié)構(gòu)變更。本文針對(duì)幾種常見的表空間回收的方式做了測(cè)試，希望給開發(fā)或運(yùn)維人員提供更穩(wěn)定的變更參考方式，保障生產(chǎn)環(huán)境的穩(wěn)定性。

各類工具對(duì)比

1.比pt-online-schema-change的trigger對(duì)原表影響較小

pt-online-schema-change的工作原理是創(chuàng)建和源表A一樣的表A_gst執(zhí)行DDL操作，同時(shí)在A上創(chuàng)建一個(gè)DML觸發(fā)器，然后將A中的數(shù)據(jù)拷貝到A_gst，在拷貝過程中產(chǎn)生的增量變更就用觸發(fā)器完成同步更新。拷貝結(jié)束后執(zhí)行兩張表的rename操作完成變更。

2.OnlineSchemaChange

工作原理和pt-online-schema-change基本一致，不同的地方是它采用的是異步模式，在A_gst的基礎(chǔ)上創(chuàng)建了一張日志表，觸發(fā)器的條目更新將直接落在日志表中，后臺(tái)進(jìn)程將日志表中的條目應(yīng)用到A_gst表。這樣整個(gè)流程上是異步的，也能夠控制回放速度。

3.gh-ost

與上面兩種變更流程基本一致，但是沒有使用觸發(fā)器的設(shè)計(jì)，所以增量變更的數(shù)據(jù)來(lái)源不是觸發(fā)器，而是Binlog文件。訂閱讀取該文件中A表的變更記錄，將記錄解析并應(yīng)用到A_gst表。這樣的數(shù)據(jù)對(duì)于gst表回放非常有利，binlog中存儲(chǔ)的都是A表的記錄，易于直接讀取和應(yīng)用。

4.DMS的無(wú)鎖結(jié)構(gòu)變更

采用了無(wú)觸發(fā)器的設(shè)計(jì)，能有效解決觸發(fā)器設(shè)計(jì)帶來(lái)的鎖、數(shù)據(jù)庫(kù)開銷等問題。同時(shí)和DTS的聯(lián)動(dòng)，執(zhí)行表空間回收時(shí)會(huì)把臨時(shí)表也傳送到DTS，這樣DTS的同步下游鏈路不會(huì)中斷。

為了驗(yàn)證DMS的無(wú)鎖變更的穩(wěn)定性，做了4次測(cè)試分別是：

編號(hào)34221藍(lán)色曲線，基準(zhǔn)oltp_insert測(cè)試作為對(duì)比基線;
編號(hào)34222綠色曲線，基準(zhǔn)oltp_insert測(cè)試+DMS的無(wú)鎖變更+ALTER TABLE [tbname] ENGINE=INNODB;
編號(hào)34237黃色曲線，基準(zhǔn)oltp_insert測(cè)試+關(guān)閉DMS的無(wú)鎖變更+RDS kernel ALTER TABLE [tbname] ENGINE=INNODB;
編號(hào)34239灰色曲線，基準(zhǔn)oltp_insert測(cè)試+關(guān)閉DMS的無(wú)鎖變更+RDS kernel OPTIMIZE TABLE [tbname];

以藍(lán)色基線為基準(zhǔn)，從圖中可以看出綠色曲線相較于同樣是執(zhí)行回收表空間的黃色和灰色平穩(wěn)，但持續(xù)時(shí)間較長(zhǎng)；綠色、黃色、灰色曲線到最后都會(huì)臨時(shí)表重命名成正式表的過程，最多2s。

測(cè)試結(jié)論

結(jié)合實(shí)際業(yè)務(wù)來(lái)說(shuō)推薦性能比較穩(wěn)定的DMS無(wú)鎖變更+ALTER TABLE。使用DMS的無(wú)鎖變更可以打開DMS控制臺(tái)，在頁(yè)面頂部，選擇全部功能 > 數(shù)據(jù)方案 > 無(wú)鎖變更。