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="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.
Explain of the query
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)
same query
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)
Please help me finding out the exact cause.
