通常來說，性能監控類業務場景具有數據導入量大、表空間增長快的特點，為了避免磁盤空間被佔滿，並提高SQL執行效率，要定期對歷史數據進行清理。根據數據採集頻率和保留週期的不同，可在應用程序中植入不同的定時器用於刪除歷史數據。在業務上線初期，這種簡單的定時清理機制是有效的，但隨著業務增長，特別是當有數據激增的情況發生時，上述定時器有很大機率會失效，不僅無法清理數據，還會因事務長時間持有表鎖，引起數據庫阻塞和流控。
下面我就跟大家分享一個因清理機制失效引發數據庫故障的案例，並且給出如何通過分區表和存儲過程進行數據清理的工程方案。
一、問題回顧
今年年初我們生產環境曾短暫發生雲監控系統故障。經排查故障是由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 NULL COMMENT '性能採集時間',
`VM_ID` varchar(80) NOT NULL COMMENT '虛擬機ID',
`PROCESSOR_USED` varchar(100) DEFAULT NULL COMMENT 'CPU利用率(%)',
`MEM_USED` varchar(100) DEFAULT NULL COMMENT '內存的使用率(%)',
`MEM_UTILITY` varchar(100) DEFAULT NULL COMMENT '可用內存量(bytes)',
`BYTES_IN` varchar(100) DEFAULT NULL COMMENT '流入流量速率(Mbps)',
`BYTES_OUT` varchar(100) DEFAULT NULL COMMENT '流出流量速率(Mbps)',
`PROC_RUN` varchar(100) DEFAULT NULL COMMENT 'CPU運行隊列中進程個數',
`WRITE_IO` varchar(100) DEFAULT NULL COMMENT '虛擬磁盤寫入速率(Mb/s)',
`READ_IO` varchar(100) DEFAULT NULL COMMENT '虛擬磁盤讀取速率(Mb/s)',
`PID` varchar(36) NOT NULL,
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_deion,2,8) ;
IF RETROWS = 0 THEN
SET @sql = CONCAT( 'ALTER TABLE ', SCHEMANAME, '.', TABLENAME, ' ADD PARTITION (PARTITION ', PARTITION_NAMES, ' VALUES LESS THAN ( "',LESS_THAN_TIMES, '" ));' );
PREPARE STMT FROM @sql;
EXECUTE STMT;
DEALLOCATE PREPARE STMT;
INSERT INTO history_partition VALUES (@sql, now());
END IF;
END IF;
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等語句的執行性能是有所提升的。
閱讀更多 小小小的發明家 的文章
