时间：2023-01-20 09:34:05 | 栏目：Mysql | 点击：次
本文Percona Blog 的译文，原文移步文章末尾的 阅读原文。
pt-archiver 是一款常见的 表清理或者归档工具。
MySQL 中删除大表之前可以使用 pt-archiver 批量删除所有记录。这样助于避免在某些情况下您的服务器可能会意外的情况，比如磁盘 IO 满导致数据库hang或者影响正常 SQL 慢查。
笔者最近遇到一个案例 ，有客户反馈 "使用 pt-archiver 删除数据时，最后一行数据未被删除。这个是不是bug?"
在解决客户的问题之前，我们需要解释为什么在删除大表之前使用 pt-archiver 当我们在 MySQL 中删除一个表时, MySQL 系统会做如下动作:
删除表数据/索引 (ibd) 和定义 (frm) 文件。
删除触发器。
通过删除要删除的表来更新表定义缓存。
扫描 InnoDB 缓冲池以查找关联页面以使其无效。--内存到的表会遇到系统hang。
需要注意的是，DROP 是一个 DDL 语句，它需要持有元数据锁 (MDL) 才能完成，这样会导致所有其他线程必须等待DDL完成，清除表相关的大量数据页会对缓冲池产生额外的压力。
最后，table_definition_cache 操作需要 LOCK_open mutex 来清理，这会导致所有其他线程等待直到删除完成。
为了降低此操作的严重性，我们可以使用 pt-archiver 通过批量的形式删除大量数据，从而显着降低表大小。一旦我们从大表中删除了记录，DROP 操作就会快速进行而不会对系统性能产生影响。
社区成员注意到此行为，在 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 进行数据归档时，也会发生同样的情况。我们的工具 pt-archiver 似乎没有将最大值复制到目标表。
将表从 tt1 迁移到 tt2 [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)
通读 pt-archiver 文档，有一个选项 –[no]safe-auto-increment 描述了用法：“不要使用 max AUTO_INCREMENT 归档行。”
这意味着，选项 –safe-auto-increment（默认）添加了一个额外的 WHERE 子句，以防止 pt-archiver 在提升单列 AUTO_INCREMENT 时删除最新的行，如下面的代码部分所示：
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 8.0之前的版本 重新启动（崩溃或其他）时，计数器将重置为最大值。如果发生这种情况并且表正在接受写入，则 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，而在于参数选项。在处理 AUTO_INCREMENT 列时使用 pt-archiver 时，了解使用 –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 选项的原理和作用 。在我们得出一切都很好的结论之前,让我们多考虑一下选项本身存在的意义。
默认情况下，–no-delete 操作应包含 –no-safe-auto-increment 选项。目前，safe-auto-increment 是默认行为。当我们使用 pt-archiver 的 --no-delete 选项时，没有删除操作。这意味着 safe-auto-increment 不应成为关注的原因。
对于 MySQL 8.0，不需要 safe-auto-increment 选项。因为 MySQL 8.0 开始，自增的值是持久化的，并且在实例重新启动或崩溃后自增的最大值不变。参考：MySQL 工作日志 https://dev.mysql.com/worklog/task/?id=6204
而且由于 MySQL 8.0 auto-increment 是通过重做日志持久化的，这使得它们成为pt-archiver 不关心的一个原因。因此，我们根本不需要 safe-auto-increment 选项。
pt-archiver 是归档 MySQL 数据的好工具，重要的是要了解所有选项以完全控制我们想要使用它实现的目标。
以后需要根据自增id进行归档的场景，pt-archiver 默认最大的id不会进行归档，需要添加参数：--no-safe-auto-increment 才能对最大id进行处理。