通常來說，性能監控類業務場景具有數據導入量大、表空間增長快的特點，為了避免磁盤空間被占滿，并提高SQL執行效率，要定期對歷史數據進行清理。根據數據采集頻率和保留周期的不同，可在應用程序中植入不同的定時器用于刪除歷史數據。在業務上線初期，這種簡單的定時清理機制是有效的，但隨著業務增長，特別是當有數據激增的情況發生時，上述定時器有很大機率會失效，不僅無法清理數據，還會因事務長時間持有表鎖，引起數據庫阻塞和流控。

[[246010]]

下面我就跟大家分享一個因清理機制失效引發數據庫故障的案例，并且給出如何通過分區表和存儲過程進行數據清理的工程方案。

一、問題回顧

今年年初我們生產環境曾短暫發生云監控系統故障。經排查故障是由OP應用程序定期在性能庫刪除數據引起的，具體原因是delete事務過大超出PXC集群同步復制寫入集，該事務在本地邏輯提交后，無法在集群另外兩個節點同步，最終在本地回滾。因持有表鎖時間過長，阻塞大量線程觸發System Lock，引起數據庫流控，最終導致華北節點云監控數據更新緩慢。

下面介紹下故障排查的過程：

1. Zabbix發出告警通知

Zabbix發出告警通知：“華北節點OP性能庫內存利用率超過80%”，時間為：2018/02/27 06:14:05。

注：OP 是“移動云”門戶系統簡稱;OP性能庫用于存放用戶訂購云產品的性能數據，架構類型為3節點的PXC多主集群架構。

登錄數據庫查看，發現等待執行的線程數量激增，數據庫已處于流控狀態。引發數據庫阻塞的SQL語句為：

DELETE FROM perf_biz_vm WHERE '2018-02-25 02:00:00'>CREATE_TIME 

該語句由OP應用程序發起，用于刪除perf_biz_vm表兩天前的歷史數據，故障發生時執行時間已超過4個小時，看執行計劃預計刪除2億行數據。

最終該語句沒有執行成功，并引發數據庫流控。

2. 故障發生的機理

這里我們結合Galera Cluster復制原理具體分析一下故障發生的機理。

首先，Galera集群節點間同步復制，主要基于廣播write set和事務驗證來實現多節點同時commit、沖突事務回滾等功能。

此外，事務在本地節點執行時采取樂觀策略，成功廣播到所有節點后再做沖突檢測，當檢測出沖突時，本地事務優先被回滾。如果沒有檢測到沖突，每個節點將獨立、異步去執行隊列中的write set。

最后，事務在本地節點執行成功返回客戶端后，其他節點保證該事務一定會被執行，Galera復制的架構圖如下：

根據Galera復制原理，刪除事務在本地節點提交成功時，本地節點把事務通過write set復制到集群另外兩個節點，之后各個節點獨立異步地進行certification test，由于要刪除的數據量非常大，該事務已超過同步復制寫入集(生產環境中write set設定值為1G)，因此，本地節點無法得到certification信息，事務并沒有插入待執行隊列進行物理提交，而是在本地優先被回滾。

錯誤日志如下：

因事務長時間持有perf_bix_vm表的X鎖，導致本地節點云主機監控數據無法入庫，隨著等待線程的累積，本地節點執行隊列會越積越長，觸發了PXC集群Flow Control機制。

該機制用于保證集群所有節點執行事務的速度大于隊列增長速度，從而避免慢節點丟失事務，實現原理是集群中同時只有一個節點可以廣播消息，每個節點都會獲得廣播消息的機會，當慢節點的執行隊列超過一定長度后，它會廣播一個FC_PAUSE消息，其他節點收到消息后會暫緩廣播消息，隨著慢節點(本地節點)事務完成回滾，直到該慢節點的執行隊列長度減少到一定程度后，Galera集群數據同步又開始恢復，流控解除。

3. 導致故障的其它因素

OP性能庫發生流控時，本地節點“DELETE FROM perf_biz_vm WHERE '2018-02-25 02:00:00'>CREATE_TIME”語句執行占滿了Buffer Pool(即生產環境innodb_buffer_ pool_size=128G)，加上數據庫本身正常運行占用的內存，使系統內存占用率超過80%預警值，此時打開華北節點OP控制臺，可以看到云監控數據更新緩慢：

4. 重建數據清理機制

截止到2月28日，歷史數據清理機制失效，導致業務表單表數據量高達250G，數據庫存儲空間嚴重不足，急需擴容。為消除數據庫安全隱患、釋放磁盤空間，我們決定在數據庫側使用分區表+存儲過程+事件的方案重建數據清理機制。

二、重建清理機制

通過分析上述故障案例，我們決定基于分區表和存儲過程建立一種安全、穩健、高效的數據庫清理機制。

通過查看執行計劃可以看到，用Delete語句刪除數據，即使在命中索引的情況下，執行效率也是很低的，而且容易觸發System lock。因此，根本解決大表數據清理問題要引入分區表，刪除數據不再執行DML操作，而是直接drop掉早期分區表(DDL)。

因為執行Delete操作時write set記錄每行信息，執行drop操作write set只是記錄表物理存放位置、表結構以及所依賴的約束、觸發器、索引和存儲過程等，當表的數據量很大時，采用drop操作要快幾個數量級。

分區表的另一個好處是對于應用程序來說不用修改代碼，通過對后端數據庫進行設置，以表的時間字段做分區字段，就可以輕松實現表的拆分，需要注意的是查詢字段必須是分區鍵，否則會遍歷所有的分區表，下面看一下具體的實施過程：

Step 1：首先，創建分區表。在這里我們就以perf_biz_vm表為例，創建相同表結構的新表，并把它命名為perf_biz_vm_new，利用create_time索引字段做分區字段，按天做分區并與主鍵一起創建聯合索引，創建語句：

代碼如下：

CREATE TABLE `perf_biz_vm_new` ( 
 
`CREATE_TIME` datetime NOT COMMENT '性能采集時間', 
 
`VM_ID` varchar(80) NOT COMMENT '虛擬機ID', 
 
`PROCESSOR_USED` varchar(100) DEFAULT COMMENT 'CPU利用率(%)', 
 
`MEM_USED` varchar(100) DEFAULT COMMENT '內存的使用率(%)', 
 
`MEM_UTILITY` varchar(100) DEFAULT COMMENT '可用內存量(bytes)', 
 
`BYTES_IN` varchar(100) DEFAULT COMMENT '流入流量速率(Mbps)', 
 
`BYTES_OUT` varchar(100) DEFAULT COMMENT '流出流量速率(Mbps)', 
 
`PROC_RUN` varchar(100) DEFAULT COMMENT 'CPU運行隊列中進程個數', 
 
`WRITE_IO` varchar(100) DEFAULT COMMENT '虛擬磁盤寫入速率(Mb/s)', 
 
`READ_IO` varchar(100) DEFAULT COMMENT '虛擬磁盤讀取速率(Mb/s)', 
 
`PID` varchar(36) NOT , 
 
PRIMARY KEY (`PID`,`CREATE_TIME`), 
 
KEY `mytable_categoryid` (`CREATE_TIME`) USING BTREE, 
 
KEY `perf_biz_vm_vm_id_create_time` (`VM_ID`,`CREATE_TIME`) 
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='虛擬機性能采集表' 
 
/*!50500 PARTITION BY RANGE COLUMNS(CREATE_TIME) 
 
(PARTITION p20180225 VALUES LESS THAN ('20180226') ENGINE = InnoDB, 
 
PARTITION p20180226 VALUES LESS THAN ('20180227') ENGINE = InnoDB, 
 
PARTITION p20180227 VALUES LESS THAN ('20180228') ENGINE = InnoDB, 
 
PARTITION p20180228 VALUES LESS THAN ('20180229') ENGINE = InnoDB, 
 
PARTITION p20180229 VALUES LESS THAN ('20180230') ENGINE = InnoDB) */ 

Step 2：用新的分區表替換原有舊表。這里需要注意的是，執行rename操作會對perf_biz_vm表的元數據進行修改，需提前檢查有無對此表的Delete、Update、Insert事務與DDL操作，否則沖突會產生元數據鎖(Metadata Lock)。

我們的做法是提前將業務側的定時器停掉，并在業務低谷時執行如下語句，將舊表和新表通過rename的方式互換，讓新表納入使用。期間若有業務調用，則會短暫斷開業務。

rename table perf_biz_vm to perf_biz_vm_old; 
rename table perf_biz_vm_new to perf_biz_vm; 

Step 3：查看到新表有數據寫入，云監控頁面數據顯示正常，說明業務恢復。云主機監控數據的保存周期是兩天，因此需要將舊表兩天前的數據拷貝到新表，該步驟通過腳本來完成，可參考以下腳本：

代碼如下：

#!/bin/bash  
function insert{  
end_time="$1 $2"  
start_time="$3 $4"  
mysql -u'user' -p'passwd' << !  
use monitor_alarm_openstack;  
set innodb_flush_log_at_trx_commit=0;  
start transaction;  
insert into perf_biz_vm select * from perf_biz_vm_old where create_time < '$end_time' and create_time > '$start_time';  
commit;  
select TABLE_ROWS from information_schema.tables where TABLE_SCHEMA ="monitor_alarm" and TABLE_NAME="perf_biz_vm";  
!  
}  
base_time="2018-02-27 2:00:00"  
while true  
do  
#end_time=$(date -d "-1hour $base_time" +%Y-%m-%d" "%H:%M:%S)  
end_time=$base_time  
start_time=$(date -d "-1hour $end_time" +%Y-%m-%d" "%H:%M:%S)  
#base_time=$end_time  
base_time=$start_time  
echo "Cur_time: $(date +%Y%m%d" "%H%M%S)" | tee -a 1.log  
echo "Range: $end_time $start_time" | tee -a 1.log  
insert ${end_time} ${start_time} | tee -a 1.log  
sleep 2  
done 

Step 4：編寫存儲過程用于定期創建新的分區，并刪除幾天前舊的分區：

代碼如下：

delimiter $$  
CREATE PROCEDURE `clean_partiton`(SCHEMANAME VARCHAR(64), TABLENAME VARCHAR(64)，reserve INT)  
BEGIN 

注：

• 該儲存過程適用于分區字段類型為datetime，按天分區且命名為p20180301格式規范的分區表
• 獲取最舊一個分區，判斷是否為reserve天前分區，是則進行刪除，每次只刪除一個分區
• 提前創建14天分區，判斷命名不重復則創建
• 創建 history_partition 表，varchar(200)和datetime類型。記錄執行成功的SQL語句

DECLARE PARTITION_NAMES VARCHAR(16);  
DECLARE OLD_PARTITION_NAMES VARCHAR(16);  
DECLARE LESS_THAN_TIMES varchar(16);  
DECLARE CUR_TIME INT;  
DECLARE RETROWS INT;  
DECLARE DROP_PARTITION VARCHAR(16);  
SET CUR_TIME = DATE_FORMAT(NOW,'%Y%m%d');  
BEGIN  
SELECT PARTITION_NAME INTO DROP_PARTITION FROM information_schema.partitions WHERE table_schema = SCHEMANAME AND table_name = TABLENAME order by PARTITION_ORDINAL_POSITION asc limit 1 ;  
IF SUBSTRING(DROP_PARTITION,2) < DATE_FORMAT(CUR_TIME - INTERVAL reserve DAY, '%Y%m%d') THEN  
SET @sql = CONCAT( 'ALTER TABLE ', SCHEMANAME, '.', TABLENAME, ' drop PARTITION ', DROP_PARTITION, ';' );  
PREPARE STMT FROM @sql;  
EXECUTE STMT;  
DEALLOCATE PREPARE STMT;  
INSERT INTO history_partition VALUES (@sql, now);  
END IF;  
end;  
SET @__interval = 1;  
create_loop: LOOP  
IF @__interval > 15 THEN  
LEAVE create_loop;  
END IF;  
SET LESS_THAN_TIMES = DATE_FORMAT(CUR_TIME + INTERVAL @__interval DAY, '%Y%m%d');  
SET PARTITION_NAMES = DATE_FORMAT(CUR_TIME + INTERVAL @__interval -1 DAY, 'p%Y%m%d');  
IF(PARTITION_NAMES != OLD_PARTITION_NAMES) THEN  
SELECT COUNT(1) INTO RETROWS FROM information_schema.partitions WHERE table_schema = SCHEMANAME AND table_name = TABLENAME AND LESS_THAN_TIMES <= substring(partition_description,2,8) ;  
IF RETROWS = 0 THEN  
SET @sql = CONCAT( 'ALTER TABLE ', SCHEMANAME, '.', TABLENAME, ' ADD PARTITION (PARTITION ', PARTITION_NAMES, ' VALUES LESS THAN ( "',LESS_THAN_TIMES, '" ));' );  
SET @__interval=@__interval+1;  
SET OLD_PARTITION_NAMES = PARTITION_NAMES;  
END LOOP;  
END  
$$  
delimiter ; 

Step 5：創建名稱為clean_perf_biz_vm的事件，并在每天凌晨00:30:00的時候調用clean_partition存儲過程創建下一個新分區，并刪除兩天前的舊分區。

delimiter | 
 
CREATE DEFINER=’root’@’localhost’ event clean_perf_biz_vm on schedule every 1 day starts DATE_ADD(DATE_ADD(CURDATE,INTERVAL 1 DAY),INTERVAL 30 MINUTE) 
 
ON COMPLETION PRESERVE 
 
do 
 
begin 
 
call clean_partition(‘monitor_alarm’,’perf_biz_vm’,’2’); 
 
end | 
 
delimiter； 

Step 6：處理perf_biz_vm_old舊表，在業務低谷期執行如下操作：drop table if exists perf_biz_vm_old，Drop掉整張舊表的時間約為3min，并釋放了150G的磁盤空間。需要注意的是，雖然drop table的時間較短，仍會產生短暫的阻塞，因為drop table觸發的是實例鎖，因此需要在業務低谷期進行操作，并實時觀察數據庫情況。

從下圖可以看到，實際drop過程中記錄到的等待接收隊列的長度瞬時值為169，最高達到202：

至此，改造全部完成，我們已在數據庫側建立起安全、穩健、高效的數據清理機制。

三、結語

雖然本方案強調了存儲過程的使用，但上述存儲過程是基于簡單的create和drop操作，并沒有涉及復雜的邏輯和計算。MySQL是OLTP應用，最擅長的還是增、刪、查、改這樣簡單的操作，對邏輯計算分析類的應用并不適合，所以盡量避免使用復雜的存儲過程。

當然，也并不是所有場景都適合使用分區表，在很多DBA看來分區表在某些場景下是禁止使用的，一般會采用切表的形式進行拆分，本方案中使用時間做分區字段，應用程序中查詢語句基本都能命中分區，對于Select、Insert等語句的執行性能是有所提升的。