扫码关注官方微信

扫码下载APP
返回顶部
首页 > 资讯 > 后端开发 > 其他教程 >关于pt-archiver和自增主键的那些事
  • 378

0
分享到

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

2024-04-02 19:04:59 378人浏览 安东尼
摘要

目录前言分析解析结论本文Percona Blog 的译文，原文移步文章末尾的 阅读原文。 前言 pt-arcHiver 是一款常见的 表清理或者归档工具。 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  `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 选项的原理和作用 。在我们得出一切都很好的结论之前,让我们多考虑一下选项本身存在的意义。

  • 默认情况下，–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进行处理。

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

--结束END--

本文标题: 关于pt-archiver和自增主键的那些事

本文链接: https://lsjlt.com/news/147271.html(转载时请注明来源链接)

有问题或投稿请发送至: 邮箱/279061341@qq.com    QQ/279061341

猜你喜欢
  • 关于pt-archiver和自增主键的那些事
    目录前言分析解析结论本文Percona Blog 的译文，原文移步文章末尾的 阅读原文。 前言 pt-archiver 是一款常见的 表清理或者归档工具。 MySQL 中删除大表之前...
    99+
    2024-04-02
  • pt-archiver和自增主键的问题怎么解决
    今天小编给大家分享一下pt-archiver和自增主键的问题怎么解决的相关知识点，内容详细，逻辑清晰，相信大部分人都还太了解这方面的知识，所以分享这篇文章给大家参考一下，希望大家阅读完这篇文章后有所收获，下面我们一起来了解一下吧。前言pt-...
    99+
    2023-06-30
  • 关于 sudo 的那些事儿
    觉得你已经了解了 sudo 的所有知识了吗？再想想。大家都知道 sudo，对吗？默认情况下，该工具已安装在大多数 Linux 系统上，并且可用于大多数 BSD 和商业 Unix 变体。不过，在与数百名 ...
    99+
    2023-06-05
  • 关于iOS自适应cell行高的那些事儿
    前言 其实早就准备写这篇文章了，但是一直没有系统去整理一下相关的demo，加上最近离职了，各种事情忙的有点郁闷，所以一直拖沓了下来。回家休息了一段时间想起来写了一半的demo，在还...
    99+
    2022-05-31
    cell 自适应 行高
  • 关于spring5的那些事:@Indexed 解密
    目录哪些资源会被索引？如何使用？原理随着云原生的发展，很多技术会被重新掂量，重新定义，历来技术的发展也是遵循天时地利，以其势尽享其利。再云原生下，jdk的最大的问题在于笨重（几百mb...
    99+
    2024-04-02
  • Java基础7：关于Java类和包的那些事
    更多内容请关注微信公众号【Java技术江湖】这是一位阿里 Java 工程师的技术小站，作者黄小斜，专注 Java 相关技术：SSM、SpringBoot、MySQL、分布式、中间件、集群、Linux、网络、多线程，偶尔讲点Docker、EL...
    99+
    2023-06-02
  • 浅谈React Router关于history的那些事
    如果你想理解React Router，那么应该先理解history。更确切地说，是history这个为React Router提供核心功能的包。它能轻松地在客户端为项目添加基于loc...
    99+
    2024-04-02
  • 关于@Controller和@Restcontroller的那点奇葩事
    目录@Controller和@Restcontroller直接甩正事后来的经验总结@Controller和@RestController的区别？@Controller和@Restco...
    99+
    2024-04-02
  • 关于索引我能说的那些事儿
    本文是自己对MySQL的InnoDB索引的理解，如有错误，还望不吝指出。 1 索引   索引两个大字往那里一摆，刚接触不久的朋友可能对这个概念有点陌生，不好理解。没有关系，先用一个简单的例子入手，比方说现在我们要从一本字典中查...
    99+
    2015-01-11
    关于索引我能说的那些事儿
  • 关于vue中api统一管理的那些事
    目录前情提要针对小项目而言（没有单独二次封装axios）无需管理，直接干。仅限于接口数量在20-30的统一api.js文件管理针对非小型项目而言（进行axios的二次封装）api统一...
    99+
    2024-04-02
  • mysql自增主键的用途有哪些
    确保每条记录都具有唯一的标识，避免重复插入数据。 简化数据的管理和操作，可以方便地根据自增主键来查询、更新和删除数据。 在一些情况...
    99+
    2024-04-09
    mysql
  • 关于mybatis的一级缓存和二级缓存的那些事儿
    目录一、缓存是什么二、为什么需要缓存三、哪些数据会放到缓存四、mybatis一级缓存五、二级缓存六、注意事项一、缓存是什么 缓存其实就是存储在内存中的临时数据，这里的数据量会比较小，...
    99+
    2024-04-02
  • 详解关于spring bean名称命名的那些事
    目录前言02源码查看01从main方法直接调试断点02带着问题查看，靠猜加验证的方式03源码验证04总结前言 用了多年spring，一直想当然把spring默认的beanName当成...
    99+
    2024-04-02
  • Mysql主键UUID和自增主键的区别及优劣分析
    引言 之前有段时间用postgresql 数据库，在上云之后，从自增主键变为uuid，感觉uuid全球唯一，很方便。 最近用mysql，发现mysql主键都是选择自增主键，仔细比较一下，为什么mysql选择自增主键，...
    99+
    2022-06-01
    Mysql 主键UUID 自增主键
  • 关于C语言操作符的那些事（超级全）
    目录前言操作符的分类移位操作符位操作符赋值操作符单目操作符关系操作符逻辑操作符条件操作符逗号表达式下标引用符函数调用符结构体调用操作符总结前言 C语言中操作符不多，但是有些相同的操作...
    99+
    2024-04-02
  • 关于主键的知识点有哪些
    本篇内容介绍了“关于主键的知识点有哪些”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！1. UUID模式通用...
    99+
    2024-04-02
  • Java基础学习之关键字和变量数据类型的那些事
    目录一. 关键字二. 变量2.1 变量的定义2.2 变量的分类1. 按照数据类型分类三. 字符编码补充：变量的声明和初始化总结一. 关键字 Java中的关键字是由特定的单词组成，单词...
    99+
    2024-04-02
  • Mysql中INNODB自增主键的问题有哪些
    小编给大家分享一下Mysql中INNODB自增主键的问题有哪些，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！背景： &nbs...
    99+
    2024-04-02
  • 详细聊聊关于Mysql联合查询的那些事儿
    目录联合查询之union 1. 查询中国各省的ID以及省份名称 2. 湖南省所有地级市ID、名字 3. 用union将他们合并 联合查询之union a...
    99+
    2024-04-02
  • Tungsten Fabric入门宝典丨关于安装的那些事（下）
    作者：Tatsuya Naganawa  译者：TF编译组Tungsten Fabric入门宝典系列文章，来自技术大牛倾囊相授的实践经验，由TF中文社区为您编译呈现，旨在帮助新手深入理解TF的运行、安装、集成、调试等全流程。如果您...
    99+
    2023-06-03
软考高级职称资格查询
热门wiki
近期文章
推荐阅读
热门问答
热门标签
编程网，编程工程师的家园，是目前国内优秀的开源技术社区之一，形成了由开源软件库、代码分享、资讯、协作翻译、讨论区和博客等几大频道内容，为IT开发者提供了一个发现、使用、并交流开源技术的平台。

  • 官方手机版

  • 微信公众号

  • 商务合作