Preface       Sometimes,we are determined to kill some MySQL connections which are occupying huge resources(CPU,memory,etc) that maybe lead to server crash.Even though it's simple to use "kill id;"(id is a number of MySQL thread which we can get from "show processlist;") command of MySQL doing that kind of job.But it's not enough in function.   Introduce       pt-kill is a commonly used tool in killing MySQL connections.It provides more individual functions by set different options than MySQL kill command.You can also use it to print the candidates to be killed instead of really killing them.Let's see some details of the tool below.
   Procedure   Usage 
1 pt-kill [OPTIONS] [DSN]

 

Common parameters 
 1 --busy-time -- Match queries that running longer than this time which match the "Command=Query" status.
 2 --idle-time -- Match querries that idle/sleeping longer than this time which match the "Command=Sleep" status.
 3 --victims -- Specify the scope which can be killed(default "oldest",others "all","all-but-oldest").
 4 --match-user -- Specify a user which you want to match.
 5 --match-host -- Specify a host which you want to match.
 6 --match-db -- Specify a db which you want to match.
 7 --match-command -- Specify a command type which you want to match.
 8 --match-state -- Specify a state type which you want to match.
 9 --match-info -- Specify a query which you want to match.
10 --test-matching -- Only used to match queries in file which contains the snapshot of processlist.(disable --run-time,--interval and --[no]ignore_self)
11 --run-time -- Specify the max running time(default unit is second).
12 --interval -- Specify the frequency for queries(default 30s if not set --interval).
13 --kill -- Execute a real kill command for those matched queries to kill both connections and queries.
14 --kill-query -- Do like what --kill does but merely queries,it does not kill connections.
15 --kill-busy-command -- It is usually used to kill the execute statement instead of query statement(eg.--kil-busy-command=execute).
16 --print -- Just print a kill statement instead of really execute them for matched queries.
17 --query-count -- Match query class if it has reached this value.

 

SRE实战 互联网时代守护先锋，助力企业售后服务体系运筹帷幄！一键直达领取阿里云限量特价优惠。
Demonstrate of mutually exclusive options. 
Specify at least one of --kill, --kill-query, --print, --execute-command or --stop.
--any-busy-time & --each-busy-time.--kill & --kill-query.
--daemonize & --test-matching.

 

1. Print all kill statement of query threads which have been run over 10s. 
1 pt-kill -h192.168.56.100 -P3306 -urepl --ask-pass --victims=all --busy-time=10 --print

 

2. Kill all the query threads which have been run over 10s. 
1 pt-kill -h192.168.56.100 -P3306 -urepl --ask-pass --victims=all --busy-time=10 --kill

 

3. Kill all the query threads which have been run over 10s in every 60s. 
1 pt-kill -h192.168.56.100 -P3306 -urepl --ask-pass --victims=all --busy-time=10 --interval=60 --kill

 

4. Kill all the execute threads which have been run over 10s. 
1 pt-kill -h192.168.56.100 -P3306 -urepl --ask-pass --victims=all --busy-time=10 --match-command=execute --kill

 

5. Print all the query threads which have been run over 10s in processlist. 
1 mysql -e "show processlist;" > proclist.log
2 pt-kill --test-matching=proclist.log --busy-time=10 --print

 

Summary
  • pt-kill is a quite useful tool to fast and simply kill threads of MySQL.
  • It's a good behaviour using "--print" before you really execute killing operation.
  • By default,the replication thread will not be killed except for using "--replication-threads" option.
  • Notice,don't be confused with "--run-time" & "--busy-time",they're totally different.
 
扫码关注我们
微信号：SRE实战
拒绝背锅 运筹帷幄