Why pt-kill hangs in connecting to MySQL?

leafonsword · December 30, 2020, 4:22pm

I could connect to local MySQL successfully:

gf&#64;li741-164 ~> mysql -uroot -S /run/mysqld/mysqld.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 202
Server version: 5.6.21-70.1 Source distribution

Copyright (c) 2009-2014 Percona LLC and/or its affiliates
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

but when using pt-kill,it seems hangs to connect to MySQL,I waited for 5 minutes ,but it still hangs:

gf&#64;li741-164 ~> pt-kill --busy-time 6 --user root --print --socket /run/mysqld/mysqld.sock

scott.nemes · December 30, 2020, 4:32pm

Hi leafonsword;

Do you know if there were actually any queries running for over 6 seconds during that time period? I do not believe there is any output from the tool unless pt-kill sees a query that meets the --busy-time you specify.

-Scott

leafonsword · December 30, 2020, 4:32pm

There is actually an uncommitted long running transaction when pt-kill executes:

mysql> show processlist;
+-----+------+-----------------+------+---------+------+-------+------------------+-----------+---------------+
| Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined |
+-----+------+-----------------+------+---------+------+-------+------------------+-----------+---------------+
| 195 | root | localhost:31769 | test | Sleep | 1710 | | NULL | 3 | 3 |
| 203 | root | localhost | NULL | Sleep | 1 | | NULL | 0 | 0 |
| 204 | root | localhost | NULL | Query | 0 | init | show processlist | 0 | 0 |
+-----+------+-----------------+------+---------+------+-------+------------------+-----------+---------------+
3 rows in set (0.00 sec)

scott.nemes · December 30, 2020, 4:32pm

Hi leafonsword;

By default pt-kill is only killing running queries, where the “command” (current state) is “query”. It appears you are attempting to kill stale connections where the command would be “sleep” (that may or may not have some junk left over like you mentioned). For that, I would look into the --match-command option.

However if you are going to kill queries that are just sleeping, I would recommend waiting longer than the six seconds you specified previously otherwise you’ll probably be creating more churn than it’s worth as it takes time and system resources to keep opening and closing the connections constantly and may result in unexpected results in your application.

http://www.percona.com/doc/percona-toolkit/2.2/pt-kill.html#cmdoption-pt-kill–match-command

-Scott

leafonsword · December 30, 2020, 4:32pm

scott.nemes;28771:

Hi leafonsword;

By default pt-kill is only killing running queries, where the “command” (current state) is “query”. It appears you are attempting to kill stale connections where the command would be “sleep” (that may or may not have some junk left over like you mentioned). For that, I would look into the --match-command option.

However if you are going to kill queries that are just sleeping, I would recommend waiting longer than the six seconds you specified previously otherwise you’ll probably be creating more churn than it’s worth as it takes time and system resources to keep opening and closing the connections constantly and may result in unexpected results in your application.

http://www.percona.com/doc/percona-toolkit/2.2/pt-kill.html#cmdoption-pt-kill–match-command

-Scott

Thx for your explaining！