pt-kill 是一個(gè)優(yōu)秀的kill MySQL連接的一個(gè)工具，是percona toolkit的一部分，在因?yàn)榭臻e連接較多導(dǎo)致超過(guò)最大連接數(shù)、某個(gè)有問(wèn)題的sql導(dǎo)致mysql負(fù)載很高時(shí)，都需要將一些連接kill掉，這個(gè)工具主要就是這個(gè)用途。

參數(shù)

–busy-time

運(yùn)行時(shí)間

–idle-time

空閑時(shí)間

–victims

所有匹配的連接，對(duì)應(yīng)有最久的連接

–interval

間隔時(shí)間，默認(rèn)30s，有點(diǎn)長(zhǎng)，可以根據(jù)實(shí)際情況來(lái)調(diào)節(jié)

–print

打印出來(lái)kill掉的連接

–match-command

匹配當(dāng)前連接的命令

Query
Sleep
Binlog Dump
Connect
Delayed insert
Execute
Fetch
Init DB
Kill
Prepare
Processlist
Quit
Reset stmt
Table Dump
–match-state

匹配當(dāng)前連接的狀態(tài)

Locked
login
copy to tmp table
Copying to tmp table
Copying to tmp table on disk
Creating tmp table
executing
Reading from net
Sending data
Sorting for order
Sorting result
Table lock
Updating
–match-info

使用正則表達(dá)式匹配符合的sql

–match-db –match-user –match-host

見(jiàn)名知意

常用用法

殺掉空閑鏈接

pt-kill –match-command Sleep –idle-time 5 –host –port –interval –print –kill –victims all

殺掉運(yùn)行時(shí)間超過(guò)5s的鏈接

pt-kill –match-command Query –busy-time 5 –host –port –interval –print –kill –victims all

殺掉匹配某個(gè)規(guī)則的正在運(yùn)行的sql

pt-kill –match-command Query –busy-time 5 –host –port –interval –print –kill –victims all –match-info

殺掉正在進(jìn)行filesort的sql

pt-kill –match-command Query –match-state “Sorting result” busy-time 5 –host –port –interval –print –kill –victims all

殺掉正在Copying to tmp table的sql

pt-kill –match-command Query –match-state “Copying to tmp table” busy-time 5 –host –port –interval –print –kill –victims all