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 2 3 4 5 6 7 8 9 | +-----+------+-----------+------+---------+------+------------------------------+-----------------------------+ | Id | User | Host | db | Command | Time | State | Info | +-----+------+-----------+------+---------+------+------------------------------+-----------------------------+ | 103 | root | localhost | test | Query | 0 | init | show processlist | | 104 | root | localhost | test | Sleep | 383 | | NULL | | 106 | root | localhost | test | Query | 377 | Waiting for table level lock | SELECT * FROM t FOR UPDATE | | 107 | root | localhost | test | Query | 364 | Waiting for table level lock | insert into t value(5) | | 108 | rpt | localhost | test | Query | 345 | Waiting for table level lock | SELECT c1 FROM t FOR UPDATE | +-----+------+-----------+------+---------+------+------------------------------+-----------------------------+ |
1. Shell script example:
1 2 3 4 5 6 7 8 9 10 | List all queries from rpt user having query time greater than 1 minute: [root@test3 ~]# for id in $(mysql -t -e "SHOW FULL PROCESSLIST" | /bin/awk -F "|" '($6 ~ /Query/) && (60< $7) && ($3 ~ /rpt/) { print $2}'); do echo "KILL QUERY $id;"; done KILL QUERY 108; Kill all queries from rpt user having query time greater than 1 minute: [root@test3 ~]# for id in $(mysql -t -e "SHOW FULL PROCESSLIST" | /bin/awk -F "|" '($6 ~ /Query/) && (60< $7) && ($3 ~ /rpt/) { print $2}'); do mysql -e "KILL QUERY $id;"; done |
2. Pt-kill example:
List all queries from rpt user having query time greater than 1 minute (–print):
1 2 3 4 5 | [root@test3 ~]# pt-kill --busy-time 60 --match-command Query --user root --print --match-user rpt --socket /tmp/mysql.sock # 2014-11-12T03:51:01 KILL 108 (Query 485 sec) SELECT c1 FROM t FOR UPDATE # 2014-11-12T03:51:31 KILL 108 (Query 515 sec) SELECT c1 FROM t FOR UPDATE # 2014-11-12T03:52:01 KILL 108 (Query 545 sec) SELECT c1 FROM t FOR UPDATE |
Kill all queries from rpt user having query time greater than 1 minute (–kill-query):
1 2 3 | [root@test3 ~]# pt-kill --busy-time 60 --match-command Query --user root --print --kill-query --match-user rpt --socket /tmp/mysql.sock # 2014-11-12T03:53:26 KILL QUERY 108 (Query 630 sec) SELECT c1 FROM t FOR UPDATE |
Process list:
1 2 3 4 5 6 7 8 9 10 | +-----+------+-----------+------+---------+------+------------------------------+----------------------------+ | Id | User | Host | db | Command | Time | State | Info | +-----+------+-----------+------+---------+------+------------------------------+----------------------------+ | 103 | root | localhost | test | Query | 0 | init | show processlist | | 104 | root | localhost | test | Sleep | 843 | | NULL | | 106 | root | localhost | test | Query | 837 | Waiting for table level lock | SELECT * FROM t FOR UPDATE | | 107 | root | localhost | test | Query | 824 | Waiting for table level lock | insert into t value(5) | | 108 | rpt | localhost | test | Sleep | 805 | | NULL | | 111 | root | localhost | NULL | Sleep | 25 | | NULL | +-----+------+-----------+------+---------+------+------------------------------+----------------------------+ |
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.
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