数据库表数据存档后的统计功能怎么样？

作为一个MySQL DBA，可以说对pt-archiver不应该有任何的无知。作为pt-toolkit套件的重要成员，它往往可以轻松帮助DBA解决数据归档的问题。例如，对于一个在线运行列表，业务只需要存储最近三个月的运行数据，并对三个月前的数据进行归档，那么pt-archiver就可以轻松帮助你完成这个任务，甚至你可以将其配置为自动任务，无需人工干预。

作为一个DBA，我们应该知道为什么，这样我们才能放心的使用pt工具。相信很多DBA都研究过pt-online-schema-change的原理，所以今天我们就来深入挖掘一下pt-archiver的工作原理。

第一，原则观察

本机有本机方法，所以我们直接打开通用日志来观察pt-archiver如何完成归档。

命令

pt-Archiver-source h = 127 . 0 . 0 . 1，u=xucl，p=xuclxucl，P=3306，D=xucl，t=t1-dest h = 127 . 0 . 0 . 1，P=3306，u=xucl，p=xuclxucl，D=xucl_archive，t = t 1

-statistics-charset = utf8mb 4-limit = 10000-txn-size 1000-sleep 30

常见选项

-分析

指定在数据存档后对表执行“分析表”操作的工具。指定诸如'- analyze=ds '之类的方法，其中s代表源表，d代表目标表，或者可以分别指定它们。

-询问-通过

命令提示符下输入密码，保护密码安全，前提是安装Perl-$ TERM模块读取密钥。

-缓冲器

指定缓冲区数据刷新到由选项'- file '指定的文件，并在提交时刷新。

只有在提交事务时，才会禁用对由'- file '指定的文件的自动刷新和刷新到磁盘，这意味着文件是由操作系统块刷新的，因此在提交事务之前，某些数据会隐式刷新到磁盘。默认情况下，在每一行操作之后，将文件刷新到磁盘。

-批量删除

在单个语句中指定删除chunk的方式来批量删除行将隐式执行选项'- commit-each '。

使用单个DELETE语句删除每个chunk对应的表行，通常的做法是按主键删除行，这样会大大提高批量删除的速度，但是如果有复杂的‘WHERE’条件可能会慢一些。

-[否]批量删除-限制

默认值:是

指定将选项'- bulk-delete '和'- limit '添加到正在存档的语句中。

-批量插入

使用LOAD DATA LOCAL INFILE方法通过成批插入块来插入行(隐式指定选项'- bulk-delete '和'- commit-each ')。

与逐行插入相比，它比在单行中插入insert语句更快。通过隐式创建一个临时表来存储需要批量插入的行(块)，而不是直接进行批量插入操作，在临时表中完成每个块后，再进行统一的数据加载。为了保证数据的安全性，这个选项会强制选择'- bulk-delete '，这样可以有效保证插入完全成功后再进行删除。

频道

指定主从复制环境为多源时，需要归档哪个主数据库，适用于多源复制中多个主数据库对应一个从数据库的情况。

-字符集，-A

指定连接字符集。

-[否]检查字符集

默认值:是

指定该检查确保连接数据库时字符集和表字符集相同。

-[否]检查-列

默认值:是

指定一个检查以确保由选项'- source '指定的源表和由'- dest '指定的目标表具有相同的字段。

不检查表中字段的排序和字段类型，只检查源表和目标表中是否都有字段。如果存在不同的字段差异，工具将报告错误并退出。如果您需要禁用此检查，请指定'-no - no-check-columns '。

-从动检查-延迟

在指定主从复制延迟大于选项'- max-lag '指定的值后，暂停归档操作。默认情况下，该工具检查所有从属库，但是该选项只对指定的从属库有效(通过DSN连接)。

-检查时间间隔

默认值:1s

如果还指定了选项'- check-slave-lag-lag '，则该选项指定的时间是工具发现主从复制延迟时暂停的时间。每操作100行检查一次。

-列，-c

指定要存档的表字段，如果有多个字段，请用'，'(逗号)分隔。

-提交-每个

指定根据一次获取和归档的行数进行提交，这将禁用选项'- txn-size '。

在每个表数据采集和归档之后，在采集下一个数据和选项'- sleep '指定的休眠时间之前，提交事务并刷新选项'- file '指定的文件，事务大小由选项'- limit '控制。

-主机，-h

指定连接的数据库的IP地址。

-端口，-P

指定连接的数据库端口。

-用户，-u

指定连接的数据库用户。

-密码，-p

指定连接的数据库用户的口令。

-插座，-S

指定使用套接字文件进行连接。

-数据库，-d

指定连接的数据库。

-来源

指定需要存档的表。该选项必须由DSN指定和表示。

-目的地

指定要归档到的目标结束表，用DSN表示。

如果未指定此选项，则由选项'- source '指定的源表在默认情况下是同一个表。

-哪里

通过WHERE条件语句指定要存档的数据。必须指定该选项。您不需要添加“WHERE”关键字。如果真的不需要WHERE条件来限制，指定'-where 1 = 1 '。

-文件

指定需要将表数据归档到的文件。使用类似MySQL DATE_FORMAT()的格式命名方法。

该文件的内容使用与MySQL中SELECT INTO OUTFILE语句相同的格式，文件命名选项如下:

%Y:年份，4位数(年份，数字，4位数)

%m:月，两位数(月，数字(01...12))

%d:天，两位数(一个月中的某一天，数字(01...31))

%H:小时(小时(00...23))

%i:分钟，数字(00...59))

%s:秒(00...59))

%D:数据库名称。

%t:表名

例如:-file '/var/log/archive/% y-% m-% d-% d，% t '

-输出格式

指定选项'- file '文件内容的输出格式。

默认情况下，该选项不被指定为字段的制表符分隔符。如果指定了此选项，则使用'，'(逗号)作为字段分隔符，使用' " '(双引号)将字段括起来。用法示例:“- output-format=dump = dump”。

-for-更新

指定将FOR UPDATE子句添加到为每个归档文件执行的SELECT语句中。-共享锁

指定将LOCK IN SHARE MODE子句添加到为每个归档文件执行的SELECT语句中。

-标题

指定将字段名作为标题写入文件的第一行。

-忽略

指定将IGNORE选项添加到INSERT语句中。

-限制

默认值:1

指定每个语句中获取表和存档表的行数。

-本地

指定不将优化和分析语句写入binlog。

-最大延迟

默认值:1s

指定主从复制的最大允许延迟时间，以秒为单位。如果在每次行数据采集后，主从延迟超过指定值，归档操作将在选项'- check-interval '指定的值内暂停。睡眠时间结束后，再次检查主从延迟时间，检查方法通过从库中查询' Seconds_Behind_Master '值来确定。如果主-从复制延迟始终大于此参数指定的值，或者从库中复制停止，操作将等待，直到从库重新启动并且延迟小于此参数指定的值。

-否-删除

指定不删除存档的表数据。

-优化

指定在数据存档后对表执行“优化表”操作的工具。指定诸如'- analyze=ds '之类的方法，其中s代表源表，d代表目标表，或者可以分别指定它们。

-仅主键

指定仅存档主键字段，这是选项'-columns = primary key '的缩写。

如果删除时工具存档操作最有效，因为只需要读取主键的一个字段，而不需要读取该行的所有字段。

-进步

指定打印进度信息的行数、当前时间、已用时间和要存档的行数。

-清洗

指定要执行的清除操作，而不是存档操作。可以忽略选项'- dest '和'- file '，如果只有清除操作可以和选项'-primary-key-only '结合使用，效率会更高。

-安静-问

指定工具静默执行，不输出任何执行信息。

-更换

指定write选项'- dest '以在指定目标结束表时将INSERT语句重写为REPLACE语句。

-重试次数

默认值:1

指定归档操作遇到死锁或超时时的重试次数。当重试次数超过此选项指定的值时，工具将出错退出。

-运行时间

指定工具存档操作需要运行多长时间才能退出。允许的时间后缀称为s=秒，m=分钟，h=小时，d=天。如果未指定，则默认为s..

-[否]安全-自动递增

默认值:是

指定对应于AUTO_INCREMENT最大值的行不用于存档。

该选项将在存档和清除时添加一个额外的WHERE子句，以防止工具删除单列升序字段中AUTO_INCREMENT属性最大值的数据行。为了在数据库重启后仍然使用AUTO_INCREMENT对应的值，会导致字段最大值的行无法存档或清除。

-设置变量

默认值:

wait_timeout=10000

innodb _ lock _ wait _ time out = 1

锁定等待超时=60

存档工具时指定参数值。如果有多个值，请用'，'(逗号)分隔。比如'-set-vars = wait _ timeout = 5000 '。

-跳过外键检查

使用SET FOREIGN_KEY_CHECKS = 0语句指定禁用外键检查。

-睡觉

指定通过SELECT语句获取归档数据时工具需要休眠的时间。默认值是不休眠。休眠前不会提交事务，也不会刷新选项'- file '指定的文件。如果指定了选项'- commit-each '，事务提交和文件刷新将在休眠之前执行。

-统计

指定工具收集并打印操作的时间统计信息。

统计数据的示例如下:

'

开始于2008-07-18t 07:18:53，结束于2008-07-18t 07:18:53

来源:D =数据库，t =表格

选择4

插入4

删除4

动作计数时间百分比

提交10 0.1079 88.27

选择5 0.0047 3.87

删除4 0.0028 2.29

插入4 0.0028 2.28

其他0 0.0040 3.29

-事务大小

默认值:1

指定每个事务的行数。如果为0，则交易功能被禁用。

-版本

显示工具的版本并退出。

-[否]版本检查

默认值:是

查看Percona Toolkit、MySQL等程序的最新版本。

-为什么-退出

指定工具将退出的原因，而不是由于已完成的存档行数。

在执行自动存档任务时，将此选项与选项'- run-time '一起使用非常方便，以便确定存档任务是否在指定时间内完成。如果还指定了选项'- statistics '，将打印所有退出原因。

二、原理分析

根据一般日志的输出，我们整理出时间序列表如下。

三。其他解释

乍一看，这个过程似乎没有问题，但如果在从原始表中扫描数据并将其插入新表的过程中，旧数据发生了变化，怎么办？

带着这个问题，我们追踪了源代码，我们破解了pt-archiver的6839行。

然后我在几个会话窗口分别做了以下动作。

最后，pt-archiver的输出如下:

#有可用的软件更新:

经过时间计数

2020-04-08t 09:13:21 0 0

2020-04-08t 09:13:21 0 1

开始于2020-04-08t 09:13:21，结束于2020-04-08t 09:13:51

来源:A=utf8mb4，D=xucl，P=3306，h=127.0.0.1，P=...，t=t1，u=xucl

Dest: A=utf8mb4，D=xucl_archive，P=3306，h=127.0.0.1，P=...，t=t1，u=xucl

选择1

插入1

删除1

动作计数时间百分比

睡眠1 30.0002 99.89

插入1 0.0213 0.07

提交2 0.0080 0.03

选择2 0.0017 0.01

删除1 0.0005 0.00

其他0 0.0008 0.00

很明显，id=3的记录没有存档(我们在这里更改了条件列，但实际生产中可能更改了其他列，导致存档数据不准确)。

那么如何解决这种情况呢？

显然，数据库可以通过添加和排除数据库中的其他锁来防止其他程序修改相应的数据。事实上，pt-archiver已经帮助我们考虑了这种情况，pt-archiver提供了两种选择。

- for-update:向SELECT语句添加FOR UPDATE修饰符

- share-lock:向SELECT语句添加共享模式中的锁定修饰符

第四，总结

Pt-archiver作为一款归档工具，无疑是MySQL DBA日常运维的绝佳工具之一，在知道如何使用的基础上就可以知道它的原理。

归档过程中最好将归档记录加锁，以免归档数据不准确。

在主从环境下，最好控制归档过程的速度，避免主从之间的延迟。

尽量控制chunk的大小，不要做的太大，造成大交易。