Percona-Toolkit 之 pt-archiver 刪除歷史資料

pt-archiver是Percona-Toolkit工具集中的一個元件，是一個主要用於對MySQL表資料進行歸檔和清除工具。它可以將資料歸檔到另一張表或者是一個檔案中。pt-archiver在清除表資料的過程中並不會影響OLTP事務的查詢效能。對於資料的歸檔，它可以歸檔到另一臺伺服器上的另一張表，也可歸檔到一個檔案中，檔案可以用LOAD DATA INFILE進行資料裝載，這個功能其實就類似是表歷史資料的增量刪除。

對線上歷史資料的歸檔，本人直接用mysqldump後還原，但是歸檔後刪除歷史資料，透過pt-archiver感覺比寫個shell指令碼方便很多。

pt-archiver --help 引數說明主要介紹刪除歷史資料能用到的。

1、--progress 每多少行列印進度資訊

2、--limit  限制select返回的行數

3、--sleep  指定select語句休眠時間

4、--txn-size 指定多少行提交一次事務

5、--bulk-delete 用單個DELETE語句批次刪除每個行塊。該語句刪除塊的第一行和最後一行之間的每一行，隱含--commit-each

6、--dry-run 列印查詢，不做任何操作後退出

新建表employees_ptarc測試：

CREATE TABLE `employees_ptarc` ( 
`id` int(11) NOT NULL AUTO_INCREMENT,
`v_int` int(11) DEFAULT NULL, 
`v_string` varchar(50) DEFAULT NULL, 
`s_string` char(20) NOT NULL, 
PRIMARY KEY (`id`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

透過儲存過程造資料：

delimiter $$
CREATE PROCEDURE i_employees_ptarc (IN row_num INT)
BEGIN

DECLARE i INT DEFAULT 0 ;
WHILE i < row_num DO
INSERT INTO employees_ptarc (v_int, v_string, s_string)
VALUES
(
floor(1 + rand() * 1000000),
substring(
MD5(RAND()),
1,
floor(1 + rand() * 20)
),
substring(MD5(RAND()), 1, 20)
) ;
SET i = i + 1 ;
END
WHILE ; END$$

delimiter ;

call i_employees_ptarc(400000);

# pt-archiver --source h=localhost,P=7777,u=root,p='Asd.123@#',D=yoon,t=employees_ptarc,A=utf8 --purge --charset=utf8 --where "id < 400000" --progress=100  --txn-size=100 --limit=100 --bulk-delete  --statistics  --dry-run

SELECT /*!40001 SQL_NO_CACHE */ `id`,`v_int`,`v_string`,`s_string` FROM `yoon`.`employees_ptarc` FORCE INDEX(`PRIMARY`) WHERE (id < 400000) AND (`id` < '400000') ORDER BY `id` LIMIT 100
SELECT /*!40001 SQL_NO_CACHE */ `id`,`v_int`,`v_string`,`s_string` FROM `yoon`.`employees_ptarc` FORCE INDEX(`PRIMARY`) WHERE (id < 400000) AND (`id` < '400000') AND ((`id` >= ?)) ORDER BY `id` LIMIT 100
DELETE FROM `yoon`.`employees_ptarc` WHERE (((`id` >= ?))) AND (((`id` <= ?))) AND (id < 400000) LIMIT 100

生成的刪除資料語句為：DELETE FROM `yoon`.`employees_ptarc` WHERE (((`id` >= ?))) AND (((`id` <= ?))) AND (id < 400000) LIMIT 100   如果不是核心業務歷史資料，這樣操作也是沒問題的，但是對於核心庫刪除歷史資料，更希望能夠實現的刪除語句為：delete from table where id=?  

將pt-archiver的引數bulk-delete去掉，改成txn-size，並加上sleep來控制，這樣不會影響DB，如下：

# pt-archiver --source h=localhost,P=7777,u=root,p='Asd.123@#',D=yoon,t=employees_ptarc --purge --charset=utf8 --where "id <= 400000" --progress=200  --limit=200 --sleep=1 --txn-size=200  --statistics  --dry-run

SELECT /*!40001 SQL_NO_CACHE */ `id`,`v_int`,`v_string`,`s_string` FROM `yoon`.`employees_ptarc` FORCE INDEX(`PRIMARY`) WHERE (id <= 600000) AND (`id` < '400000') ORDER BY `id` LIMIT 100
SELECT /*!40001 SQL_NO_CACHE */ `id`,`v_int`,`v_string`,`s_string` FROM `yoon`.`employees_ptarc` FORCE INDEX(`PRIMARY`) WHERE (id <= 600000) AND (`id` < '400000') AND ((`id` >= ?)) ORDER BY `id` LIMIT 100
DELETE FROM `yoon`.`employees_ptarc` WHERE (`id` = ?)

檢視審計日誌：

root,localhost,79,608616,QUERY,`yoon`,'DELETE FROM `yoon`.`employees_ptarc` WHERE (`id` = \'110367\')',0

root,localhost,79,608617,QUERY,`yoon`,'DELETE FROM `yoon`.`employees_ptarc` WHERE (`id` = \'110368\')',0

root,localhost,79,608618,QUERY,`yoon`,'DELETE FROM `yoon`.`employees_ptarc` WHERE (`id` = \'110369\')',0

root,localhost,79,608619,QUERY,`yoon`,'DELETE FROM `yoon`.`employees_ptarc` WHERE (`id` = \'110370\')',0

root,localhost,79,608620,QUERY,`yoon`,'DELETE FROM `yoon`.`employees_ptarc` WHERE (`id` = \'110371\')',0

root,localhost,79,608621,QUERY,`yoon`,'DELETE FROM `yoon`.`employees_ptarc` WHERE (`id` = \'110372\')',0

root,localhost,79,608622,QUERY,`yoon`,'DELETE FROM `yoon`.`employees_ptarc` WHERE (`id` = \'110373\')',0

root,localhost,79,608623,QUERY,`yoon`,'DELETE FROM `yoon`.`employees_ptarc` WHERE (`id` = \'110374\')',0

root,localhost,79,608624,QUERY,`yoon`,'DELETE FROM `yoon`.`employees_ptarc` WHERE (`id` = \'110375\')',0

root,localhost,79,608625,QUERY,`yoon`,'DELETE FROM `yoon`.`employees_ptarc` WHERE (`id` = \'110376\')',0

root,localhost,79,608626,QUERY,`yoon`,'DELETE FROM `yoon`.`employees_ptarc` WHERE (`id` = \'110377\')',0

root,localhost,79,608627,QUERY,`yoon`,'DELETE FROM `yoon`.`employees_ptarc` WHERE (`id` = \'110378\')',0

root,localhost,79,608628,QUERY,`yoon`,'DELETE FROM `yoon`.`employees_ptarc` WHERE (`id` = \'110379\')',0

root,localhost,79,608629,QUERY,`yoon`,'DELETE FROM `yoon`.`employees_ptarc` WHERE (`id` = \'110380\')',0

root,localhost,79,608630,QUERY,`yoon`,'DELETE FROM `yoon`.`employees_ptarc` WHERE (`id` = \'110381\')',0

root,localhost,79,608631,QUERY,`yoon`,'DELETE FROM `yoon`.`employees_ptarc` WHERE (`id` = \'110382\')',0

root,localhost,79,608632,QUERY,`yoon`,'DELETE FROM `yoon`.`employees_ptarc` WHERE (`id` = \'110383\')',0

root,localhost,79,608633,QUERY,`yoon`,'DELETE FROM `yoon`.`employees_ptarc` WHERE (`id` = \'110384\')',0

root,localhost,79,608634,QUERY,`yoon`,'DELETE FROM `yoon`.`employees_ptarc` WHERE (`id` = \'110385\')',0

root,localhost,79,608635,QUERY,`yoon`,'DELETE FROM `yoon`.`employees_ptarc` WHERE (`id` = \'110386\')',0

root,localhost,79,608636,QUERY,`yoon`,'DELETE FROM `yoon`.`employees_ptarc` WHERE (`id` = \'110387\')',0

root,localhost,79,608637,QUERY,`yoon`,'DELETE FROM `yoon`.`employees_ptarc` WHERE (`id` = \'110388\')',0

root,localhost,79,608638,QUERY,`yoon`,'DELETE FROM `yoon`.`employees_ptarc` WHERE (`id` = \'110389\')',0

root,localhost,79,608639,QUERY,`yoon`,'DELETE FROM `yoon`.`employees_ptarc` WHERE (`id` = \'110390\')',0

root,localhost,79,608640,QUERY,`yoon`,'DELETE FROM `yoon`.`employees_ptarc` WHERE (`id` = \'110391\')',0

root,localhost,79,608641,QUERY,`yoon`,'DELETE FROM `yoon`.`employees_ptarc` WHERE (`id` = \'110392\')',0

root,localhost,79,608642,QUERY,`yoon`,'DELETE FROM `yoon`.`employees_ptarc` WHERE (`id` = \'110393\')',0