关于pt-archiver和自增主键的那些事

本文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完成后，该表仍有一行待处理。

#Createdtable
mysql>CREATETABLE`tt1`(
`id`int(11)NOTNULLAUTO_INCREMENT,
`a`char(5)DEFAULTNULL,
PRIMARYKEY(`id`)
)ENGINE=InnoDB

#Pouredrandomtestdataintoit
mysql>callpopulate('test','att1',10000,'N');

#Purgeddatausingpt-archiver
[root@centos_2~]#pt-archiver--source=h=localhost,D=test,t=tt1--purge--where"1=1"

#Verifyingcount(expected0,got1)
mysql>selectcount(*)fromtest.tt1;
+----------+
|count(*)|
+----------+
|1|
+----------+
1rowinset(0.00sec)

当我们使用带有--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>selectcount(*)fromtt2;
+----------+
|count(*)|
+----------+
|5008|
+----------+
1rowinset(0.00sec)

mysql>selectcount(*)fromtt1;
+----------+
|count(*)|
+----------+
|5009|
+----------+
1rowinset(0.00sec)

解析

通读 pt-archiver 文档，有一个选项–[no]safe-auto-increment描述了用法：“不要使用 maxAUTO_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("SELECTMAX($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/*!40001SQL_NO_CACHE*/`id`,`a`FROM`test`.`tt1`FORCEINDEX(`PRIMARY`)WHERE(1=1)ORDERBY`id`LIMIT1
SELECT/*!40001SQL_NO_CACHE*/`id`,`a`FROM`test`.`tt1`FORCEINDEX(`PRIMARY`)WHERE(1=1)AND((`id`>))ORDERBY`id`LIMIT1
INSERTINTO`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/*!40001SQL_NO_CACHE*/`id`,`a`FROM`test`.`tt1`FORCEINDEX(`PRIMARY`)WHERE(1=1)<strong>AND(`id`<'5009')</strong>ORDERBY`id`LIMIT1
SELECT/*!40001SQL_NO_CACHE*/`id`,`a`FROM`test`.`tt1`FORCEINDEX(`PRIMARY`)WHERE(1=1)<strong>AND(`id`<'5009')</strong>AND((`id`>))ORDERBY`id`LIMIT1
INSERTINTO`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值将更改。

#deletingeverythingfromtable
mysql>deletefromtt1;
...
mysql>showtablestatuslike'tt1'\G
***************************1.row***************************
Name:tt1
Engine:InnoDB
...
Auto_increment:10019
...

#RestartingMySQL
[root@centos_2~]#systemctlrestartmysql

#Verifyingauto-incrementcounter
[root@centos_2~]#mysqltest-e"showtablestatuslike'tt1'\G"
***************************1.row***************************
Name:tt1
Engine:InnoDB
...
Auto_increment:1
...

上面的测试结果告诉我们: 这里的问题实际上并不在于pt-archiver，而在于参数选项。在处理AUTO_INCREMENT列时使用pt-archiver时，了解使用–no-safe-auto-increment选项很重要。

让我们用我们的实验室数据来验证它。

#Verifyingtheusageof–no-safe-auto-incrementoption
[root@centos_2~]#pt-archiver--source=h=localhost,D=test,t=tt1--purge--where"1=1"--no-safe-auto-increment

mysql>selectcount(*)fromtest.tt1;
+----------+
|count(*)|
+----------+
|0|
+----------+
1rowinset(0.00sec)

使用–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>selectcount(*)fromtt1;selectcount(*)fromtt2;
+----------+
|count(*)|
+----------+
|5009|
+----------+
1rowinset(0.00sec)

+----------+
|count(*)|
+----------+
|5009|
+----------+
1rowinset(0.00sec)

通过上面的代码和实际测试，我们知道了 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.0auto-increment是通过重做日志持久化的，这使得它们成为pt-archiver 不关心的一个原因。因此，我们根本不需要safe-auto-increment选项。

结论

• pt-archiver 是归档 MySQL 数据的好工具，重要的是要了解所有选项以完全控制我们想要使用它实现的目标。

• 以后需要根据自增id进行归档的场景，pt-archiver 默认最大的id不会进行归档，需要添加参数：--no-safe-auto-increment 才能对最大id进行处理。

到此这篇关于pt-archiver和自增主键的文章就介绍到这了,更多相关pt-archiver和自增主键内容请搜索源码搜藏网以前的文章或继续浏览下面的相关文章希望大家以后多多支持源码搜藏网！