清除期间卡住pt-存档查询

Question

我正在使用pt-archiver清除1.2T大小的表。在show processlist中，我可以看到Sending data状态下650个秒的查询卡住了。当我独立执行时，查询是快速的。任何帮助都将不胜感激。

[shell]# pt-archiver  --source h=localhost,D=dsm,t=subscriber_event -u XX -pXXX    --where="created <= DATE_SUB(now(), interval 1 year)" --limit 10000  --purge --statistics --progress 10000 --max-lag=100 --check-interval=15 --check-slave-lag h=X.X.X.X,S=/data/mysqllog/mysql_logs/mysql.sock --no-check-charset --why-quit  --primary-key-only --retries=5
TIME                ELAPSED   COUNT
2021-01-16T00:34:27       0       0
DBD::mysql::st execute failed: Lost connection to MySQL server during query [for Statement "SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `dsm`.`subscriber_event` FORCE INDEX(`PRIMARY`) WHERE (created <= DATE_SUB(now(), interval 1 year)) AND (`id` < '3873782070') ORDER BY `id` LIMIT 10000"] at /bin/pt-archiver line 6631.

解释查询

mysql> explain SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `dsm`.`subscriber_event` FORCE INDEX(`PRIMARY`) WHERE (created <= DATE_SUB(now(), interval 1 year)) AND (`id` < '3873782070') ORDER BY `id` LIMIT 10000;
+----+-------------+------------------+------------+-------+---------------+---------+---------+------+------------+----------+-------------+
| id | select_type | table            | partitions | type  | possible_keys | key     | key_len | ref  | rows       | filtered | Extra       |
+----+-------------+------------------+------------+-------+---------------+---------+---------+------+------------+----------+-------------+
|  1 | SIMPLE      | subscriber_event | NULL       | range | PRIMARY       | PRIMARY | 8       | NULL | 1450620612 |    33.33 | Using where |
+----+-------------+------------------+------------+-------+---------------+---------+---------+------+------------+----------+-------------+
1 row in set, 1 warning (0.02 sec)

相同查询

mysql>SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `dsm`.`subscriber_event` FORCE INDEX(`PRIMARY`) WHERE (created <= DATE_SUB(now(), interval 1 year)) AND (`id` < '3873802696') ORDER BY `id` LIMIT 10000;
| 3205169561 |
| 3205169562 |
| 3205169563 |
| 3205169564 |
+------------+
10000 rows in set (0.10 sec)

请帮我找出确切原因。

Answer 1

按日期清除表的一个更好的方法是使用DROP PARITION。请参见http://mysql.rjweb.org/doc.php/partitionmaint，不幸的是，它需要将分区添加到表中并更改某些索引。对于1.2TB来说，这将是一个非常长的(一次)任务。

同时。如果10000是可调的，则将数字降到1000。我怀疑桌面上的其他活动与归档扫描相冲突。通过减少这一数字，冲突的可能性就会减少，而牺牲的是需要更长时间的档案。(不，长度不是10倍。)

(“发送数据”是无用的信息。唉，这可能是你唯一能得到的线索了。)

(id < '3873782070')：id INT UNSIGNED吗？如果是这样的话，它会很快溢出吗？