優雅地使用pt-archiver進行資料歸檔

姬子玉發表於2017-12-05

mysql 日誌 公有云 test charset uuid source statistics

一、引言 最近由於業務需求,需要將公有云RDS(業務庫)的大表資料歸檔至私有云MySQL(歷史庫),以縮減公有云RDS的體積和成本。 那麼問題來了,資料歸檔的方式有n種,選擇哪種呢?經過一番折騰,發現使用percona的pt-archiver就可以輕鬆並優雅地對MySQL進行資料歸檔。

7317b2bf9c910bcf1ecb7c629ead4ac2ad8d6df5

一、引言

最近由於業務需求,需要將公有云RDS(業務庫)的大表資料歸檔至私有云MySQL(歷史庫),以縮減公有云RDS的體積和成本。

那麼問題來了,資料歸檔的方式有n種,選擇哪種呢?經過一番折騰,發現使用percona的pt-archiver就可以輕鬆並優雅地對MySQL進行資料歸檔。

待我娓娓道來~

1.1 pt-archive是啥

屬於大名鼎鼎的percona工具集的一員,是歸檔MySQL大表資料的最佳輕量級工具之一。

注意,相當輕,相當方便簡單。

1.2 pt-archive能幹啥

二、基本資訊

2.1 MySQL環境

0943088ab6ea0b21de9535ace36fbe33c85b9dff

2.2 pt-archiver資訊

a503ee9975545f14614ba196999d1767ec516057

2.3 歸檔表資訊

aca6666fb35f4edd98f881c77e5b05b78ad84a9d

注意:pt-archiver操作的表必須有主鍵

d4c8f64a3e0ada4a2210dd5c690fa60663554d00

三、模擬場景

3.1 場景1-1:全表歸檔,不刪除原表資料,非批量插入


pt-archiver \ --source h=10.73.129.187,P=3306,u=backup_user,p='xxx',D=test123,t=c1 \ --dest h=10.73.129.188,P=3306,u=backup_user,p='xxx',D=test123,t=c1 \ --charset=UTF8 --where '1=1' --progress 10000 --limit=10000 --txn-size 10000 --statistics --no-delete複製程式碼

f522591bb4ee06259c6c2b60eff97ada10d5f15d

3.2 場景1-2:全表歸檔,不刪除原表資料,批量插入

pt-archiver \ --source h=10.73.129.187,P=3306,u=backup_user,p='xxx',D=test123,t=c1 \ --dest h=10.73.129.188,P=3306,u=backup_user,p='xxx',D=test123,t=c1 \ --charset=UTF8 --where '1=1' --progress 10000 --limit=10000 --txn-size 10000 --bulk-insert --bulk-delete --statistics --no-delete複製程式碼

ae7bb93984172b6abfb9e11224de271fc61ff093

3.3 場景2-1:全表歸檔,刪除原表資料,非批量插入,非批量刪除

pt-archiver \ --source h=10.73.129.187,P=3306,u=backup_user,p='xxx',D=test123,t=c1 \ --dest h=10.73.129.188,P=3306,u=backup_user,p='xxx',D=test123,t=c1 \ --charset=UTF8 --where '1=1' --progress 10000 --limit=10000 --txn-size 10000 --statistics --purge複製程式碼

d50354a4cf8086406b7fe1d9eefbe17debbd1700

3.4 場景2-2:全表歸檔,刪除原表資料,批量插入,批量刪除

pt-archiver \ --source h=10.73.129.187,P=3306,u=backup_user,p='xxx',,D=test123,t=c1 \ --dest h=10.73.129.188,P=3306,u=backup_user,p='xxx',D=test123,t=c1 \ --charset=UTF8 --where '1=1' --progress 10000 --limit=10000 --txn-size 10000 --bulk-insert --bulk-delete --statistics --purge複製程式碼


d98ed12f1a0deb91147e8830b7e7dc63afdf07f8

四、小結

4.1 效能對比

通過下表可以看出,批量操作和非批量操作的效能差距非常明顯,批量操作花費時間為非批量操作的十分之一左右。

68d0ecbe4dd224e4f8c141fa8b5bc4afe3cf3cd1

場景2-1:全表歸檔,刪除原表資料,非批量插入,非批量刪除4.2 general log分析

源庫general log:

  1. set autocommit=0
  2. 批量查詢(對應引數limit)
SELECT /*!40001 SQL_NO_CACHE */ `uuid` FORCE 

INDEX(`PRIMARY`) WHERE (1=1) AND ((`uuid` >= '266431')) 

ORDER BY `uuid` LIMIT 10000複製程式碼

3. 逐行刪除

DELETE FROM `test123`.`c1` WHERE (`uuid` = '000002f0d9374c56ac456d76a68219b4')複製程式碼

4. COMMIT(對應引數--txn-size,運算元量達到--txn-size,則commit)

目標庫general log:

  1. set autocommit=0
  2. 逐行插入
INSERT INTO `test123`.`c1`(`uuid`) VALUES ('0436dcf30350428c88e3ae6045649659')複製程式碼

3. COMMIT(對應引數--txn-size,運算元量達到--txn-size,則commit)

場景2-2:全表歸檔,刪除原表資料,批量插入,批量刪除

源庫:

  1. set autocommit=0
  2. 批量查詢(對應limit引數)
SELECT /*!40001 SQL_NO_CACHE */ `uuid` FORCE 

INDEX(`PRIMARY`) WHERE (1=1) AND ((`uuid` >= '266431')) 

ORDER BY `uuid` LIMIT 10000複製程式碼

3. 批量刪除

DELETE FROM `test123`.`c1` WHERE (((`uuid` >= '266432'))) AND (((`uuid` <= '273938'))) AND (1=1) LIMIT 10000複製程式碼

4. COMMIT(對應引數--txn-size,運算元量達到--txn-size,則commit)

目標庫:

  1. set autocommit=0
  2. 批量插入
LOAD DATA LOCAL INFILE '/tmp/vkKXnc1VVApt-archiver' INTO TABLE `test123`.`c1`CHARACTER SET UTF8(`uuid`)複製程式碼

3. COMMIT(對應引數--txn-size,運算元量達到--txn-size,則commit)

五、附錄

常用引數

a2a6fa0100cd34c226f95c9649e9bdbb628fd115


原文釋出時間為:2017-11-28

本文作者:藍劍鋒@知數堂

本文來自雲棲社群合作伙伴“老葉茶館”,瞭解相關資訊可以關注“老葉茶館”微信公眾號

原文連結


相關文章