Use Pt-kill to kill a connection based on certain rules _mysql

Pt-kill is a good kill MySQL connection tool, is a part of Percona Toolkit, in the due to more idle connections cause more than the maximum number of connections, a problem of SQL causes MySQL load is high, you need to kill some connections, This tool is mainly used for this purpose.

Parameters

–busy-time

Run time

–idle-time

Free time

–victims

All matching connections, with the longest connection

–interval

Interval time, the default 30s, a bit long, can be adjusted according to the actual situation

–print

Print out a Kill connection

–match-command

The command that matches the current connection

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

Match the status of the current connection

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

Use regular expressions to match compliant SQL

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

See the name of the idea

Common usage

Kill off the free links

Pt-kill–match-command Sleep–idle-time 5–host–port–interval–print–kill–victims All

Kill a link that runs longer than 5s

Pt-kill–match-command Query–busy-time 5–host–port–interval–print–kill–victims All

Kill the running SQL that matches a rule

Pt-kill–match-command query–busy-time 5–host–port–interval–print–kill–victims All–match-info

Kill the SQL that's being Filesort

Pt-kill–match-command query–match-state "Sorting result" busy-time 5–host–port–interval–print–kill–victims all

Kill the SQL that is copying to TMP table

Pt-kill–match-command query–match-state "Copying to tmp table" Busy-time 5–host–port–interval–print–kill–victims A ll