background
- Archived tables exist in both source and target Libraries
- The scenarios of pt-archiver archive table are as follows: no deletion of original table data, non-batch insertion of target database; no deletion of original table data, batch insertion of target database; non-batch deletion of original table data, non-batch insertion of target database; batch deletion of original table data, batch insertion of target database.
Edition
pt-archiver --version
pt-archiver 3.0.12
select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.12 |
+-----------+
Is there any inconsistency?
- The source library has been delete d, and the target library has not been insert yet.
- The target library is insert, and the source library is not delete yet.
--bulk-insert
Using LOAD DATA INFILE method, compared with inserting one line by one line, by creating temporary files for each batch of data, first writing data to temporary files, when a batch of data has been acquired, then importing operation, accelerating the speed of target library insertion.
--bulk-delete
Batch deletion, a batch of data lines completed with a DELETE statement
Generate 100,000 records
sysbench /usr/local/share/^Csbench/oltp_read_write.lua --mysql_storage_engine=innodb --table-size=100000 --tables=1 --mysql-db=test_archiver --mysql-user=admin --mysql-password=admin --mysql-port=8013 --mysql-host=127.0.0.1 --threads=8 --time=10 --report-interval=1 --events=0 --db-driver=mysql prepare
Does the source and target libraries have inconsistency testing in different instances?
Source sink
192.168.137.133:test_archiver
Target library
192.168.137.1:test_archiver
Open gerneral log
set global general_log=on;
commit every 5,000 records and process every 10,000 records
nohup pt-archiver --source h=127.0.0.1,u=admin,p=admin,P=8013,D=test_archiver,t=sbtest1 --dest h=192.168.137.1,u=admin,p=admin,P=3306,D=test_archiver --progress 1000 --where "id<100000" --statistics --limit 10000 --sleep 10 --no-check-charset --txn-size 5000 --bulk-delete --bulk-insert &
kill the pt-archiver archiving process halfway, and there is no inconsistency between the source and target Libraries
ps -ef | grep pt-archiver | awk '{print $2}' | xargs kill -9
Target library
select id from sbtest1 order by id desc limit 1; +-------+ | id | +-------+ | 10000 | +-------+ 1 row in set (0.00 sec)
Source sink
select id from sbtest1 order by id limit 1; +-------+ | id | +-------+ | 10001 | +-------+ 1 row in set (0.00 sec)
Source Library Execution Statement
2019-08-21T07:02:58.600832Z 56 Connect admin@127.0.0.1 on test_archiver using TCP/IP 2019-08-21T07:02:58.601186Z 56 Query set autocommit=0 ... 2019-08-21T07:02:58.966036Z 56 Query SELECT MAX(`id`) FROM `test_archiver`.`sbtest1` 2019-08-21T07:02:58.967807Z 56 Query SELECT CONCAT(@@hostname, @@port) 2019-08-21T07:02:58.989394Z 56 Query SELECT /*!40001 SQL_NO_CACHE */ `id`,`k`,`c`,`pad` FROM `test_archiver`.`sbtest1` FORCE INDEX(`PRIMARY`) WHERE (id<100000) AND (`id` < '100000') ORDER BY `id` LIMIT 10000 ... 2019-08-21T07:02:59.275620Z 56 Query commit ... 019-08-21T07:02:59.532682Z 56 Query commit 2019-08-21T07:02:59.834194Z 56 Query SELECT 'pt-archiver keepalive' 2019-08-21T07:02:59.834835Z 56 Query DELETE FROM `test_archiver`.`sbtest1` WHERE (((`id` >= '1'))) AND (((`id` <= '10000'))) AND (id<100000) LIMIT 10000 2019-08-21T07:03:09.958289Z 56 Query SELECT /*!40001 SQL_NO_CACHE */ `id`,`k`,`c`,`pad` FROM `test_archiver`.`sbtest1` FORCE INDEX(`PRIMARY`) WHERE (id<100000) AND (`id` < '100000') AND ((`id` >= '10000')) ORDER BY `id` LIMIT 10000 ... 2019-08-21T07:03:10.215958Z 56 Query commit ... 2019-08-21T07:03:10.670937Z 56 Query commit 2019-08-21T07:03:10.904398Z 56 Query SELECT 'pt-archiver keepalive' 2019-08-21T07:03:10.904715Z 56 Query DELETE FROM `test_archiver`.`sbtest1` WHERE (((`id` >= '10001'))) AND (((`id` <= '20000'))) AND (id<100000) LIMIT 10000 ====>( The statement does not have commit Statement meeting rollback )
Target Library Execution Statement
2019-08-21T07:03:00.317343Z 33 Connect admin@192.168.137.133 on test_archiver using TCP/IP 2019-08-21T07:03:00.338390Z 33 Query set autocommit=0 ... 2019-08-21T07:03:00.633938Z 33 Query SELECT CONCAT(@@hostname, @@port) 2019-08-21T07:03:00.920655Z 33 Query commit 2019-08-21T07:03:01.177267Z 33 Query commit 2019-08-21T07:03:01.199046Z 33 Query LOAD DATA LOCAL INFILE '/tmp/jaGuzZfjSept-archiver' INTO TABLE `test_archiver`.`sbtest1`(`id`,`k`,`c`,`pad`) (Insert 1=<id <=10000 Record) 2019-08-21T07:03:11.850618Z 33 Query commit 2019-08-21T07:03:12.315829Z 33 Query commit 2019-08-21T07:03:12.337323Z 33 Query LOAD DATA LOCAL INFILE '/tmp/GQ2ybc3KCzpt-archiver' INTO TABLE `test_archiver`.`sbtest1`(`id`,`k`,`c`,`pad`) ====>( The statement does not have commit This statement will rollback ,And in the machine/tmp Leave temporary files under the directory) ll /tmp/GQ2ybc3KCzpt-archiver -rw------- 1 root root 1920000 Aug 21 15:03 /tmp/GQ2ybc3KCzpt-archiver
- As can be seen from the log, the commit time of delete operation of source library (07:03:10.215958Z) is before the commit time of insert operation of target library (07:03:11.850618Z). This may occur when the source library has deleted and the target library has not insert yet.
- This time the source and target libraries are on different instances, and the inconsistency of instance clocks will affect the time when commit appears in general_log.
Whether inconsistency testing will occur between source and target libraries in the same instance
Source sink
192.168.137.133:test_archiver
Target library
192.168.137.133:test_archiver2
Delete test data and regenerate 100,000 records
sysbench /usr/local/share/sysbench/oltp_read_write.lua --mysql_storage_engine=innodb --table-size=100000 --tables=1 --mysql-db=test_archiver --mysql-user=admin --mysql-password=admin --mysql-port=8013 --mysql-host=127.0.0.1 --threads=8 --time=10 --report-interval=1 --events=0 --db-driver=mysql cleanup
sysbench /usr/local/share/sysbench/oltp_read_write.lua --mysql_storage_engine=innodb --table-size=100000 --tables=1 --mysql-db=test_archiver --mysql-user=admin --mysql-password=admin --mysql-port=8013 --mysql-host=127.0.0.1 --threads=8 --time=10 --report-interval=1 --events=0 --db-driver=mysql prepare
Every 100,000 records are commit once, and each time 100,000 records are taken for processing.
pt-archiver --source h=127.0.0.1,u=admin,p=admin,P=8013,D=test_archiver,t=sbtest1 --dest h=127.0.0.1,u=admin,p=admin,P=8013,D=test_archiver2 --progress 1000 --where "id<100000" --statistics --sleep 10 --limit 100000 --no-check-charset --txn-size 100000 --bulk-delete --bulk-insert
Source and target library execution statements
2019-08-22T01:50:35.672490Z 9 Connect admin@127.0.0.1 on test_archiver using TCP/IP 2019-08-22T01:50:35.673125Z 9 Query set autocommit=0 ... 2019-08-22T01:50:35.685987Z 10 Connect admin@127.0.0.1 on test_archiver2 using TCP/IP 2019-08-22T01:50:35.686278Z 10 Query set autocommit=0 ... 2019-08-22T01:50:35.708866Z 9 Query SELECT /*!40001 SQL_NO_CACHE */ `id`,`k`,`c`,`pad` FROM `test_archiver`.`sbtest1` FORCE INDEX(`PRIMARY`) WHERE (id<100000) AND (`id` < '100000') ORDER BY `id` LIMIT 100000 ... 2019-08-22T01:50:40.242371Z 10 Query LOAD DATA LOCAL INFILE '/tmp/X5W2UemPgDpt-archiver' INTO TABLE `test_archiver2`.`sbtest1`(`id`,`k`,`c`,`pad`) 2019-08-22T01:50:43.692914Z 9 Query SELECT 'pt-archiver keepalive' 2019-08-22T01:50:43.693411Z 9 Query DELETE FROM `test_archiver`.`sbtest1` WHERE (((`id` >= '1'))) AND (((`id` <= '99999'))) AND (id<100000) LIMIT 100000 2019-08-22T01:50:58.603351Z 9 Query SELECT /*!40001 SQL_NO_CACHE */ `id`,`k`,`c`,`pad` FROM `test_archiver`.`sbtest1` FORCE INDEX(`PRIMARY`) WHERE (id<100000) AND (`id` < '100000') AND ((`id` >= '99999')) ORDER BY `id` LIMIT 100000 2019-08-22T01:50:58.606390Z 10 Query commit 2019-08-22T01:50:58.717251Z 9 Query commit 2019-08-22T01:50:58.780614Z 10 Quit 2019-08-22T01:50:58.781480Z 9 Quit
- From the general log, the bulk insertion of the target library is before the bulk deletion of the source library, and the commit (01:50:58.606390Z) of the insert operation of the target library is also before the commit (01:50:58.717251Z) of the delete operation of the source library.
- *** During the period of 0.11 s after the commit execution of the target database, pt-archiver terminates abnormally (this probability is very small_____). If the commit of the source database is not executed, the delete operation will roll back, resulting in inconsistency between the source database and the target database***
Be careful
In addition to setting secure_file_priv, MySQL 8.0 also needs to set local-infile=1 in [client] and [mysqld] to execute the load data infile command, otherwise errors will occur.
DBD::mysql::st execute failed: The used command is not allowed with this MySQL version
pt-archiver commit
- The relevant code for the operation can be seen that the source library does not commit until the target library completes the commit operation.
- When there is a large amount of data in the transaction, the commit operation of the source database delete will take a long time. After the PT archiver is terminated abnormally (the commit operation of the source database has not been completed, and the delete operation will roll back), there will be a situation where the target database already has data and the source database has not deleted the inconsistent data.
7068 if ( $dst ) { 7069 trace('commit', sub { 7070 $dst->{dbh}->commit; 7071 }); 7072 } 7073 trace('commit', sub { 7074 $src->{dbh}->commit; 7075 }); 7076 $txn_cnt = 0; 7077 } 7078 }
conclusion
- During the non-commit period of pt-archiver archiving, pt-archiver terminates abnormally, and both source and target libraries rollback without inconsistency.
- When pt-archiver terminates abnormally at commit, inconsistencies may occur: the target library has been insert, and the source library has not been delete d
- After the abnormal termination of pt-archiver (not filing on time, manual kill pt process, etc.), the primary key condition of the target library and source library needs to be checked manually, otherwise the primary key conflict will occur in the re-archiving.