How a set of queries can be killed in MySQL using Percona Toolkit’s pt-kill

How a set of queries can be killed in MySQL using Percona Toolkit’s pt-kill

PREVIOUS POST
NEXT POST

You might have encountered situations where you had to kill some specific select queries that were running for long periods and choking the database. This post will go into more detail with an example of report query offloading.

Report query (select) offloading to a slave server is a common practice to reduce the workload of the master DB server. The long running selects will be executed in the slave for report generation. I have observed in many cases where the slave used to get delayed or the slave DB encounters a slowdown due to some heavy long-running orphaned selects from some wrong reports.

There are two main ways to kill queries in MySQL: 1. use custom scripts that match on a regular expression, or 2. use a tool written and supported by Percona that is designed to kill queries based on matching conditions. Below is one script that will help you to kill those queries. The script will take the process list from MySQL and filter the long-running select query considering the “User”, “Time” and “State” from the list. However I suggest that you use the pt-kill tool from Percona Toolkit which provides a more reliable choice and options based on your requirements.

Process list:

1. Shell script example:

2. Pt-kill example:

List all queries from rpt user having query time greater than 1 minute (–print):

Kill all queries from rpt user having query time greater than 1 minute (–kill-query):

Process list:

 Note : –kill-query option makes pt-kill kill matching queries. This requires MySQL 5.0 or newer. Unlike –kill which kills the connection for matching queries, this option only kills the query, not its connection.

pt-kill captures queries from SHOW PROCESSLIST, filters them, and then either kills or prints them. This is also known as a “slow query sniper” in some circles. The idea is to watch for queries that might be consuming too many resources, and kill them. For details please read the tool’s (pt-kill) documentation.

PREVIOUS POST
NEXT POST

Share this post

Comment (1)

  • gopal Reply

    Hi Arunjith, I just tried to test the tool, but it seems it is not working.
    {code:sql}
    root@localhost[(none)]>show full processlist;
    +—-+——+—————–+——+———+——-+————-+———————–+
    | Id | User | Host | db | Command | Time | State | Info |
    +—-+——+—————–+——+———+——-+————-+———————–+
    | 18 | root | localhost:58651 | NULL | Sleep | 78900 | cleaning up | PLUGIN |
    | 25 | root | localhost | NULL | Sleep | 21 | | NULL |
    | 26 | root | localhost | NULL | Sleep | 138 | | NULL |
    | 27 | root | localhost | NULL | Query | 0 | starting | show full processlist |
    +—-+——+—————–+——+———+——-+————-+———————–+
    4 rows in set (0.00 sec)
    {code}

    Howevery pt-kill is not showing any output:

    gopal@D252:~/Work/percona-toolkit-2.2.17/bin$ perl pt-kill –busy-time 60 –print -uroot -pchangeme –verbose
    # 2016-06-16T13:39:47 pt-kill starting
    # 2016-06-16T13:39:47 Connected to host D252
    # 2016-06-16T13:39:47 Find spec: $VAR1 = {
    all => undef,
    busy_time => ’60’,
    idle_time => undef,
    ignore => {
    Command => undef,
    Host => undef,
    Id => 28,
    Info => undef,
    State => ‘Locked’,
    User => undef,
    db => undef
    },
    match => {
    Command => undef,
    Host => undef,
    Info => undef,
    State => undef,
    User => undef,
    db => undef
    },
    replication_threads => undef
    };

    # 2016-06-16T13:39:47 Run-time: forever at 30 second intervals
    # 2016-06-16T13:39:47 Checking processlist
    # 2016-06-16T13:39:47 Matched 0 queries
    # 2016-06-16T13:39:47 Sleeping 30 seconds after normal interval

    MySQL Version – 5.7, also tried with 5.1
    Percona-toolkit-version – 2.2.17

    June 16, 2016 at 7:41 am

Leave a Reply