Not the answer you need?
Register and ask your own question!

Why pt-kill hangs in connecting to MySQL?

leafonswordleafonsword ContributorInactive User Role Beginner
I could connect to local MySQL successfully:
gf@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@li741-164 ~> pt-kill  --busy-time 6 --user root --print --socket /run/mysqld/mysqld.sock

Comments

  • scott.nemesscott.nemes MySQL Sage Current User Role Patron
    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
  • leafonswordleafonsword Contributor Inactive User Role Beginner
    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

    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.nemesscott.nemes MySQL Sage Current User Role Patron
    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
  • leafonswordleafonsword Contributor Inactive User Role Beginner
    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!
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.