As a best practice before dropping a large table in MySQL, pt-archiver can be used to delete all the records in batches. This helps to avoid a situation where your server may get stalled under certain circumstances.
I recently read a comment from a user saying “The pt-archiver is not working as expected! It is skipping the last record, which seems like a bug.”. Let’s examine pt-archiver’s default behavior and understand why the author of this comment believes that pt-archiver is bugged (Spoiler: It’s not!).
But wait, before continuing on busting the blame, let me clarify why to use pt-archiver before dropping large tables.
When we drop a table in MySQL:
- Table data/index (ibd) and definition (frm) files are removed.
- Triggers are removed.
- Table definition cache is updated by removing the table being dropped.
- InnoDB buffer pool is scanned for associated pages to invalidate them.
Note that DROP is a DDL statement and it will require a Metadata Lock (MDL) to complete the task causing all the other threads to wait on that. This also creates additional pressure on the buffer pool for purging a large number of data pages associated with the table being dropped.
And finally, the table_definition_cache operation requires LOCK_open mutex to clean up and this causes all other threads to wait until the drop is complete.
To reduce the severity of this operation we can use pt-archiver to delete large data in small chunks, thus lowering the table size significantly. Once we have deleted records from the large table, the drop operation goes fast without creating a performance impact.
Coming back to debunking the blame. This behavior was noted by a community member that after the pt-archiver was completed, the table still had one row pending.
Generating a lab the scenarios proved that these allegations appear to be true:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | # Created table mysql> CREATE TABLE `tt1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `a` char(5) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB # Poured random test data into it mysql> call populate('test','att1',10000,'N'); # Purged data using pt-archiver [root@centos_2 ~]# pt-archiver --source=h=localhost,D=test,t=tt1 --purge --where "1=1" # Verifying count (expected 0, got 1) mysql> select count(*) from test.tt1; +----------+ | count(*) | +----------+ | 1 | +----------+ 1 row in set (0.00 sec) |
The same happens when we use pt-archiver for data copy with –no-delete. Our tool, pt-archiver, appears not to copy the max value to the destination table.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | [root@centos_2 ~]# pt-archiver --source=h=localhost,D=test,t=tt1 --dest=h=localhost,D=test,t=tt2 --no-delete --where "1=1" mysql> select count(*) from tt2; +----------+ | count(*) | +----------+ | 5008 | +----------+ 1 row in set (0.00 sec) mysql> select count(*) from tt1; +----------+ | count(*) | +----------+ | 5009 | +----------+ 1 row in set (0.00 sec) |
We have a bug report that was already in place https://jira.percona.com/browse/PT-837 noting this behavior. But is this really a bug?
Reading through the pt-archiver documentation, there’s an option –[no]safe-auto-increment which describes the usage: “Do not archive rows with max AUTO_INCREMENT.”
Meaning, the option –safe-auto-increment (default) adds an extra WHERE clause to prevent pt-archiver from removing the newest row when ascending a single-column AUTO_INCREMENT as seen in the code section below:
1 2 3 4 5 6 7 8 9 10 11 12 | https://github.com/percona/percona-toolkit/blob/3.x/bin/pt-archiver#L6449 if ( $o->get('safe-auto-increment') && $sel_stmt->{index} && scalar(@{$src->{info}->{keys}->{$sel_stmt->{index}}->{cols}}) == 1 && $src->{info}->{is_autoinc}->{ $src->{info}->{keys}->{$sel_stmt->{index}}->{cols}->[0] } ) { my $col = $q->quote($sel_stmt->{scols}->[0]); my ($val) = $dbh->selectrow_array("SELECT MAX($col) FROM $src->{db_tbl}"); $first_sql .= " AND ($col < " . $q->quote_val($val) . ")"; } |
Let’s see the difference between the two commands via dry-run output:
1 2 3 4 5 | # With --no-safe-auto-increment [root@centos_2 ~]# pt-archiver --source=h=localhost,D=test,t=tt1 --dest=h=localhost,D=test,t=tt2 --no-delete --where "1=1" <strong>--no-safe-auto-increment</strong> --dry-run SELECT /*!40001 SQL_NO_CACHE */ `id`,`a` FROM `test`.`tt1` FORCE INDEX(`PRIMARY`) WHERE (1=1) ORDER BY `id` LIMIT 1 SELECT /*!40001 SQL_NO_CACHE */ `id`,`a` FROM `test`.`tt1` FORCE INDEX(`PRIMARY`) WHERE (1=1) AND ((`id` > ?)) ORDER BY `id` LIMIT 1 INSERT INTO `test`.`tt2`(`id`,`a`) VALUES (?,?) |
1 2 3 4 5 | # Without --no-safe-auto-increment (default) [root@centos_2 ~]# pt-archiver --source=h=localhost,D=test,t=tt1 --dest=h=localhost,D=test,t=tt2 --no-delete --where "1=1" --dry-run SELECT /*!40001 SQL_NO_CACHE */ `id`,`a` FROM `test`.`tt1` FORCE INDEX(`PRIMARY`) WHERE (1=1) <strong>AND (`id` < '5009')</strong> ORDER BY `id` LIMIT 1 SELECT /*!40001 SQL_NO_CACHE */ `id`,`a` FROM `test`.`tt1` FORCE INDEX(`PRIMARY`) WHERE (1=1) <strong>AND (`id` < '5009')</strong> AND ((`id` > ?)) ORDER BY `id` LIMIT 1 INSERT INTO `test`.`tt2`(`id`,`a`) VALUES (?,?) |
Did you note the additional clause “AND (id < ‘5009’)” above?
This option of –no-safe-auto-increment guards against re-using AUTO_INCREMENT values if the server restarts. Note that the extra WHERE clause contains the maximum value of the auto-increment column as of the beginning of the archive or purge job. If new rows are inserted while pt-archiver is running, pt-archiver will not see them.
Alright, now we know the “why” of the “blame”, but why? What is the issue around safety for AUTO_INCREMENT?
The AUTO_INCREMENT counter is stored in memory and as MySQL restarts (crashes or otherwise), the counter will reset to the max value. If this happens and the table is accepting writes, the AUTO_INCREMENT value will change.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | # deleting everything from table mysql> delete from tt1; ... mysql> show table status like 'tt1'\G *************************** 1. row *************************** Name: tt1 Engine: InnoDB ... Auto_increment: 10019 ... # Restarting MySQL [root@centos_2 ~]# systemctl restart mysql # Verifying auto-increment counter [root@centos_2 ~]# mysql test -e "show table status like 'tt1'\G" *************************** 1. row *************************** Name: tt1 Engine: InnoDB ... Auto_increment: 1 ... |
These observations convincingly tell us that the problem here was not really with pt-archiver but with the option chosen. It is important to understand the use –no-safe-auto-increment option, in the case of using pt-archiver while working on AUTO_INCREMENT columns.
Let’s just verify it with our lab data.
1 2 3 4 5 6 7 8 9 10 | # Verifying the usage of –no-safe-auto-increment option [root@centos_2 ~]# pt-archiver --source=h=localhost,D=test,t=tt1 --purge --where "1=1" --no-safe-auto-increment mysql> select count(*) from test.tt1; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec) |
The same goes for a copy operation with –no-delete option.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | [root@centos_2 ~]# pt-archiver --source=h=localhost,D=test,t=tt1 --dest=h=localhost,D=test,t=tt2 --no-delete --where "1=1" --no-safe-auto-increment mysql> select count(*) from tt1; select count(*) from tt2; +----------+ | count(*) | +----------+ | 5009 | +----------+ 1 row in set (0.00 sec) +----------+ | count(*) | +----------+ | 5009 | +----------+ 1 row in set (0.00 sec) |
Now that we have established the understanding of pt-archiver’s –[no]safe-auto-increment option and before we conclude that everything is well and good; let us give the option itself some more thought.
- The –no-delete operation should by default include –no-safe-auto-increment option.
- At present, safe-auto-increment is default behavior. When we use –no-delete option of pt-archiver, there are no delete operations. This means safe-auto-increment should not be a reason for concern.
- Do you agree? Do you want to counter? Use comments.
- For MySQL 8.0, safe-auto-increment option is not required.
- This thought comes from the knowledge that from MySQL 8.0 onwards, the auto-increment values are persistent and survives restarts or crashes. –Ref: MySQL worklog
- And since MySQL 8.0 auto-increment is persisted via redo logs, this makes them a reason for not being a concern for our beloved pt-archiver. Thus we don’t need a safe-auto-increment option at all.
- Do you agree? Do you want to counter?
Well, point #2 was too convincing until it was refuted by my friend Ananias with the following facts.
- Reuse of previously allocated auto-increment counters cannot be guaranteed.
“However, in the case of an unexpected server exit, reuse of a previously allocated auto-increment value cannot be guaranteed. Each time the current maximum auto-increment value is changed due to an INSERT or UPDATE operation, the new value is written to the redo log, but if the unexpected exit occurs before the redo log is flushed to disk, the previously allocated value could be reused when the auto-increment counter is initialized after the server is restarted” – MySQL Documentation
- Yet another fact, as documentation reads “As of MySQL 8.0.21, you can disable redo logging”. Though this feature is specifically created for speeding up the data load to MySQL and is not to be used in production; “To err is human”.
- That makes the “safe-auto-increment” option to stay in 8.0+. The only option we are left with is to understand the options.
Conclusion
The pt-archiver is a great tool for archiving MySQL data and it is important to be aware of all the options to have full control of what we want to achieve using it.
It’d be interesting to know if you have any thoughts while reading this; please use comments and share.
My preferred behavior in this case would be (a) don’t delete the row with the max auto-increment value from the original table, (b) but do copy it to the other table anyway. That way, if you recreated the original table using the data from the archive table, you’d have all the data. If you kept using the original table, the auto-increment value would keep ascending. And if you copied more data from that same table to the archive table in the future, you would just get 1 row rejected due to a duplicate key.
I am the author of mentioned topic :p, I will try again with newest version =)
For me, I would to get all rows matching my query, that’s why my confusion =), Thanks Kedar, for this explanation ! When I made this test I should try to keep some data, but it’s was more simple to check that all tables were empties.