Stuck pt-archiver query during purge I am purging a table of size 1.2T using pt-archiver. In the show processlist i can see the query stuck for 650 secs in Sending data state. The query is quick when I execute independely. Any help will be appreciated.
[shell]# pt-archiver  --source h=localhost,D=dsm,t=subscriber_event -u XX -pXXX    --where=&quot;created &lt;= DATE_SUB(now(), interval 1 year)&quot; --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 &quot;SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `dsm`.`subscriber_event` FORCE INDEX(`PRIMARY`) WHERE (created &lt;= DATE_SUB(now(), interval 1 year)) AND (`id` &lt; '3873782070') ORDER BY `id` LIMIT 10000&quot;] at /bin/pt-archiver line 6631.

Explain of the query
mysql&gt; explain SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `dsm`.`subscriber_event` FORCE INDEX(`PRIMARY`) WHERE (created &lt;= DATE_SUB(now(), interval 1 year)) AND (`id` &lt; '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)

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

Please help me finding out the exact cause.
A much better way to purge a table by date is by using DROP PARITION.  See http://mysql.rjweb.org/doc.php/partitionmaint  Unfortunately, it requires adding partitioning to the table and changing some indexes.  This will be a very long (one-time) task for 1.2TB.
Meanwhile...  If 10000 is tunable, lower the number to, say 1000.  I suspect that other activities on the table are conflicting with the archiving scan.  By decreasing that number, the will be less chance of conflict, at the expense of the archive taking longer.  (No, not 10 times as long.)
(&quot;Sending data&quot; is useless information.  Alas, that is probably the only clue you can get.)
(id &lt; '3873782070'):  Is id INT UNSIGNED?  If so, might it overflow soon?