pt-archiver如何处理自增值的

作为MySQL中删除大表的最佳实践，在删除大表之前，pt-archiver可以用来批量删除表中的所有数据。这有助于在某些场景下避免数据库夯住。

最近收到一个用户的评论说"pt-archiver并不能向我们希望的那样工作！它跳过了最后一条记录，看起来是个bug"。让我们来检查以下pt-archiver的默认行为，看看为什么有用户认为遇到了bug。

但是，先等一下，让我先澄清以下为什么在删除大表之前，先使用pt-archiver。

 

什么时候会删除MySQL中的表：

·表的数据文件和定义文件已经被移除

·触发器已经被移除

·表定义缓存已经被移除表操作更新了

·innodb buffer pool相关的页被扫描并被置为无效

 

请注意，drop是ddl操作，需要施加MDL锁(元数据锁)，加锁的时候会导致其它线程产生等待。这也会对buffer pool造成压力，因为要purge大量与被删除的表相关的页。

最后，purge表定义缓存的时候，table_definition_cache操作需要施加lock_open mutex，也会导致其它线程产生等待。

为了减少删除表造成的问题，可以使用pt-archiver按chunks删除记录，chunks都比较小，因此可以很大程度上减小表的大小。一旦大表中的记录都被删除了，drop表就很快了，也不会对性能产生较大的影响。

回来上面提到的bug的问题。社区用户注意到此行为，在pt-archiver完成后，该表仍有一行待处理。

开始测试：

# 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)

使用参数-no-delete，测试结果一样。pt-archiver似乎没有将最大值拷贝到目标表。

[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)

我们已经提交了一个bug报告，但是，这真的是一个bug么?

阅读pt-archiver的官方文档，有一个选项 -[no]safe-auto-increment描述了用法：不要归档auto_increment最大的行。

这意味着，选项-safe-auto-increment(默认设置)额外增加了一个where条件，pt-archiver在按照升序移除行的时候，防止pt-archiver移除最新的行。正如上面我们看到的代码示例。

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) . ")";
   }

 

我们来空运行以下两个命令，看看区别：

# 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 (?,?)

 

# 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 (?,?)

看到没有，额外多了一个语句“ AND (id < ‘5009’)”。

 

如果服务器重新启动，–no-safe-auto-increment的这个选项可以防止重新使用AUTO_INCREMENT值。 请注意，额外的WHERE子句包含自归档或清除作业开始时自动增量列的最大值。如果在pt-archiver运行时插入新行，pt-archiver将看不到它们。

好了，现在我们知道原因了，但为什么呢？AUTO_INCREMENT存在的安全问题是什么？

AUTO_INCREMENT计数器存储在内存中，当MySQL重新启动（崩溃或其他）时，计数器将重置最大值。如果发生这种情况并且表正在接受写入，则AUTO_INCREMENT值将更改。

# 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
...

从这里可以看到，问题并不是pt-archiver引起，而是参数选项的设置问题。使用pt-archiver的时候，如果有auto_increment的列，理解参数选项-no-safe-auto-increment的含义很重要。

在我的测试环境下验证一下：

# 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)

使用-no-delete参数后，结果一样：

[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)

到这里，我们已经理解了pt-archiver的-[no]safe-auto-increment选项的用途。

 

目前，safe-auto-increment是默认设置。当我们使用-no-delete选项的时候，不会有删除操作。就不用关心safe-auto-increment了。

从MySQL8.0开始，safe-auot-increment选项是不需要的。从8.0开始，自增值持久化机制发生了变化了。从8.0开始，自增值被持久化在redo日志。但其实并不是如此，一些特殊的场景，比如关闭了redo，并不能保证自增值的持久化得到保证，还是需要的。