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`)複製程式碼