Introduction to the Pt-kill use of Percona utility _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.

Obtain a connection that satisfies the condition from show processlist or read the connection that satisfies the condition from the file containing show processlist and print or kill or perform other actions, this tool is very practical in work, When the server connection has an exception the first thought is Pt-kill, write a script can also, but the function is still not pt-kill strong; Generally I like to put the necessary information to connect local MySQL in a file file, when you need to connect local MySQL directly specify the configuration file;

Common function Introduction
Pt-kill--defaults-file xx--match-command--kill--victims all--interval 10 kill the number of connections in the sleep state every 10s;

Pt-kill defaults-file xx--busy-time--kill--victims all--interval 10 every 10s to kill the initial runnning state more than 60s connection number;

several important parameters
--daemonize in the background in the form of daemon operation;
How often--interval run, the unit can be s,m,h,d, etc. default is S
--victims default is oldest, only kill the oldest query. This is not really a long run of inquiries to prevent being killed, they just wait for a long time. This kind of match is queried by time, killing a maximum of time.
All kill all the satisfied threads
Kill all but the longest keep not kill

Action
--kill Kill the connection and exit.
--kill-query only kills the statement that the connection executes, but the thread is not terminated
--print Print statements that meet the criteria

QUERY matches
--busy-time Batch query has been running longer than this time thread;
--idle-time kill how many hours of sleep the connection thread must be active at--match-command.

For more information, refer to the official documentation: https://www.percona.com/doc/percona-toolkit/2.1/pt-kill.html

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