mysql 日誌 公有云 test charset uuid source statistics
一、引言 最近由於業務需求，需要將公有云RDS（業務庫）的大表資料歸檔至私有云MySQL（歷史庫），以縮減公有云RDS的體積和成本。 那麼問題來了，資料歸檔的方式有n種，選擇哪種呢？經過一番折騰，發現使用percona的pt-archiver就可以輕鬆並優雅地對MySQL進行資料歸檔。
最近由於業務需求，需要將公有云RDS（業務庫）的大表資料歸檔至私有云MySQL（歷史庫），以縮減公有云RDS的體積和成本。
那麼問題來了，資料歸檔的方式有n種，選擇哪種呢？經過一番折騰，發現使用percona的pt-archiver就可以輕鬆並優雅地對MySQL進行資料歸檔。
屬於大名鼎鼎的percona工具集的一員，是歸檔MySQL大表資料的最佳輕量級工具之一。
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複製程式碼
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複製程式碼
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複製程式碼
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複製程式碼
通過下表可以看出，批量操作和非批量操作的效能差距非常明顯，批量操作花費時間為非批量操作的十分之一左右。
場景2-1：全表歸檔，刪除原表資料，非批量插入，非批量刪除4.2 general log分析
- 從日誌看起來，源庫的查詢和目標庫的插入有先後順序
- 從日誌看起來，目標庫的插入和源庫的刪除，並無先後順序。在特定條件下，萬一目標庫插入失敗，源庫刪除成功，咋搞？感覺這裡並不十分嚴謹
- 刪除採用DELETE FROM TABLE WHERE ... ，每次刪除一行資料
- 插入採用INSERT INTO TABLE VALUES('...')，每次插入一行資料
SELECT /*!40001 SQL_NO_CACHE */ `uuid` FORCE
INDEX(`PRIMARY`) WHERE (1=1) AND ((`uuid` >= '266431'))
ORDER BY `uuid` LIMIT 10000複製程式碼DELETE FROM `test123`.`c1` WHERE (`uuid` = '000002f0d9374c56ac456d76a68219b4')複製程式碼4. COMMIT(對應引數--txn-size，運算元量達到--txn-size，則commit)
INSERT INTO `test123`.`c1`(`uuid`) VALUES ('0436dcf30350428c88e3ae6045649659')複製程式碼3. COMMIT(對應引數--txn-size，運算元量達到--txn-size，則commit)
- 從日誌看起來，源庫的批量查詢和目標庫的批量插入有先後順序
- 從日誌看起來，目標庫的批量插入和源庫的批量刪除，並無先後順序。
- 批量刪除採用DELETE FROM TABLE WHERE ... LIMIT 10000
- 批量插入採用LOAD DATA LOCAL INFILE 'file' INTO TABLE ...
SELECT /*!40001 SQL_NO_CACHE */ `uuid` FORCE
INDEX(`PRIMARY`) WHERE (1=1) AND ((`uuid` >= '266431'))
ORDER BY `uuid` LIMIT 10000複製程式碼DELETE FROM `test123`.`c1` WHERE (((`uuid` >= '266432'))) AND (((`uuid` <= '273938'))) AND (1=1) LIMIT 10000複製程式碼4. COMMIT(對應引數--txn-size，運算元量達到--txn-size，則commit)
LOAD DATA LOCAL INFILE '/tmp/vkKXnc1VVApt-archiver' INTO TABLE `test123`.`c1`CHARACTER SET UTF8(`uuid`)複製程式碼3. COMMIT(對應引數--txn-size，運算元量達到--txn-size，則commit)
