EmergencyEMERGENCY? Get 24/7 Help Now!

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

 | November 19, 2014 |  Posted In: Insight for DBAs, MySQL, Percona Software, Percona Toolkit

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
Arunjith Aravindan

Arunjith Aravindan is on Percona's consulting team and has several years experience in MySQL database and Linux administration at companies that include Amrita Technologies in addition to large U.S.-based travel technology firms. His main interests are databases and operating systems, specifically technologies like MySQL, Linux and Shell scripting. His specialities include installations, configurations, replication, backups, restores, log analysis, shell scripts, cron jobs, query analysis and crash recovery of databases (and much more).

One Comment

  • 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

Leave a Reply