Are there inconsistencies between the source and target libraries of pt-archiver archived data?

Posted by Altairzq on Sun, 29 Sep 2019 16:40:28 +0200

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.

Detailed Use of pt-archiver Archiving Tool

pt-archiver
pt-archiver data archiving

Topics: MySQL Database