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

[[246010]]

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

一、問題回顧

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

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

1. Zabbix發(fā)出告警通知

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

注：OP 是“移動云”門戶系統(tǒng)簡稱;OP性能庫用于存放用戶訂購云產(chǎn)品的性能數(shù)據(jù)，架構(gòu)類型為3節(jié)點的PXC多主集群架構(gòu)。

登錄數(shù)據(jù)庫查看，發(fā)現(xiàn)等待執(zhí)行的線程數(shù)量激增，數(shù)據(jù)庫已處于流控狀態(tài)。引發(fā)數(shù)據(jù)庫阻塞的SQL語句為：

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

該語句由OP應(yīng)用程序發(fā)起，用于刪除perf_biz_vm表兩天前的歷史數(shù)據(jù)，故障發(fā)生時執(zhí)行時間已超過4個小時，看執(zhí)行計劃預(yù)計刪除2億行數(shù)據(jù)。

最終該語句沒有執(zhí)行成功，并引發(fā)數(shù)據(jù)庫流控。

2. 故障發(fā)生的機理

這里我們結(jié)合Galera Cluster復(fù)制原理具體分析一下故障發(fā)生的機理。

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

此外，事務(wù)在本地節(jié)點執(zhí)行時采取樂觀策略，成功廣播到所有節(jié)點后再做沖突檢測，當(dāng)檢測出沖突時，本地事務(wù)優(yōu)先被回滾。如果沒有檢測到?jīng)_突，每個節(jié)點將獨立、異步去執(zhí)行隊列中的write set。

最后，事務(wù)在本地節(jié)點執(zhí)行成功返回客戶端后，其他節(jié)點保證該事務(wù)一定會被執(zhí)行，Galera復(fù)制的架構(gòu)圖如下：

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

錯誤日志如下：

因事務(wù)長時間持有perf_bix_vm表的X鎖，導(dǎo)致本地節(jié)點云主機監(jiān)控數(shù)據(jù)無法入庫，隨著等待線程的累積，本地節(jié)點執(zhí)行隊列會越積越長，觸發(fā)了PXC集群Flow Control機制。

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

3. 導(dǎo)致故障的其它因素

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

4. 重建數(shù)據(jù)清理機制

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

二、重建清理機制

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

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

因為執(zhí)行Delete操作時write set記錄每行信息，執(zhí)行drop操作write set只是記錄表物理存放位置、表結(jié)構(gòu)以及所依賴的約束、觸發(fā)器、索引和存儲過程等，當(dāng)表的數(shù)據(jù)量很大時，采用drop操作要快幾個數(shù)量級。

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

Step 1：首先，創(chuàng)建分區(qū)表。在這里我們就以perf_biz_vm表為例，創(chuàng)建相同表結(jié)構(gòu)的新表，并把它命名為perf_biz_vm_new，利用create_time索引字段做分區(qū)字段，按天做分區(qū)并與主鍵一起創(chuàng)建聯(lián)合索引，創(chuàng)建語句：

代碼如下：

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 '內(nèi)存的使用率(%)', 
 
`MEM_UTILITY` varchar(100) DEFAULT COMMENT '可用內(nèi)存量(bytes)', 
 
`BYTES_IN` varchar(100) DEFAULT COMMENT '流入流量速率(Mbps)', 
 
`BYTES_OUT` varchar(100) DEFAULT COMMENT '流出流量速率(Mbps)', 
 
`PROC_RUN` varchar(100) DEFAULT COMMENT 'CPU運行隊列中進(jìn)程個數(shù)', 
 
`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：用新的分區(qū)表替換原有舊表。這里需要注意的是，執(zhí)行rename操作會對perf_biz_vm表的元數(shù)據(jù)進(jìn)行修改，需提前檢查有無對此表的Delete、Update、Insert事務(wù)與DDL操作，否則沖突會產(chǎn)生元數(shù)據(jù)鎖(Metadata Lock)。

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

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

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

代碼如下：

#!/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：編寫存儲過程用于定期創(chuàng)建新的分區(qū)，并刪除幾天前舊的分區(qū)：

代碼如下：

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

注：

• 該儲存過程適用于分區(qū)字段類型為datetime，按天分區(qū)且命名為p20180301格式規(guī)范的分區(qū)表
• 獲取最舊一個分區(qū)，判斷是否為reserve天前分區(qū)，是則進(jìn)行刪除，每次只刪除一個分區(qū)
• 提前創(chuàng)建14天分區(qū)，判斷命名不重復(fù)則創(chuàng)建
• 創(chuàng)建 history_partition 表，varchar(200)和datetime類型。記錄執(zhí)行成功的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：創(chuàng)建名稱為clean_perf_biz_vm的事件，并在每天凌晨00:30:00的時候調(diào)用clean_partition存儲過程創(chuàng)建下一個新分區(qū)，并刪除兩天前的舊分區(qū)。

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舊表，在業(yè)務(wù)低谷期執(zhí)行如下操作：drop table if exists perf_biz_vm_old，Drop掉整張舊表的時間約為3min，并釋放了150G的磁盤空間。需要注意的是，雖然drop table的時間較短，仍會產(chǎn)生短暫的阻塞，因為drop table觸發(fā)的是實例鎖，因此需要在業(yè)務(wù)低谷期進(jìn)行操作，并實時觀察數(shù)據(jù)庫情況。

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

至此，改造全部完成，我們已在數(shù)據(jù)庫側(cè)建立起安全、穩(wěn)健、高效的數(shù)據(jù)清理機制。

三、結(jié)語

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

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