文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

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

2024-04-02 19:55

关注

本文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  `id`,`a` FROM `test`.`tt1` FORCE INDEX(`PRIMARY`) WHERE (1=1) ORDER BY `id` LIMIT 1
SELECT  `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  `id`,`a` FROM `test`.`tt1` FORCE INDEX(`PRIMARY`) WHERE (1=1) <strong>AND (`id` < '5009')</strong> ORDER BY `id` LIMIT 1
SELECT  `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 选项的原理和作用 。在我们得出一切都很好的结论之前,让我们多考虑一下选项本身存在的意义。

而且由于 MySQL 8.0 auto-increment 是通过重做日志持久化的,这使得它们成为pt-archiver 不关心的一个原因。因此,我们根本不需要 safe-auto-increment 选项。

结论

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

阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-后端开发
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯