Introduction to es1EN ES2en utility

pt-kill is a good kill MySQL connection tool. It is the first part of percona toolkit. When the maximum number of connections is exceeded due to too many idle connections and the load on mysql is high due to some problematic sql, the main purpose of this tool is to drop one of the connections.

From show processlist get satisfy the condition of connection or from contains show processlist file read meet the conditions of connection and print or kill or perform other operations, the tool work in practicality is very high, when the server connection abnormal after the first think of is pt - kill, write a script can also, but still no pt - kill powerful; 1 Generally, I like to put the necessary information to connect to local MySQL in a file file. When I need to connect to local mysql, I can directly specify the configuration file.

Introduction to Common functions
pt-kill -- ES27en-ES28en xx -- ES30en-ES31en Sleep --kill --interval 10 Kill the number of connections in the sleep state every 10s;

pt-kill file xx 60 --kill all --interval 10 The initial runnning state at every 10s kill exceeds the connection number of 60s;

Several important parameters
--daemonize is placed in the background to run as a daemon;
-- How often does interval run? The unit can be s,m,h, d, etc. The default is s
victims, which is oldest by default, kills only the oldest queries. This is to prevent being killed by queries that are not really running for a long time, they are just waiting for a long time. This kind of match by time query, kill 1 time maximum value.
all kills all satisfied threads
Kill all, but keep the longest without killing

action
--kill kills the connection and exits
-- ES79en-ES80en only kills the statement executed by the connection, but the thread is not terminated
--print prints statements that meet the criteria

QUERY MATCHES
-- Es87EN-ES88en batch query threads that have been running longer than this;
How much time does it take to kill the connection thread of sleep? It must be at -- ES93en-ES94en sleep to be effective

More introduction can refer to the official document: https: / / www percona. com/doc/percona - toolkit / 2.1 / pt - kill. # html cmdoption pt - kill - interval

parameter

� busy - time

The elapsed time

� idle - time

Free time

� victims

All matched connections should have the longest connection

� interval

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

� print

Print out kill dropped 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

Matches the state 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 the corresponding sql

� match - db � match - user � match - host

See of knowledge meaning

Common usage

Kill idle links

pt-kill by ES234en-ES238en 5 by host by port by interval by kill by all by host by all

Kill links that take longer than 5s

pt-kill by ES253en-ES257en 5 by host by host by print by kill by all by host by host by time by host by print by kill by 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 sql who is working on filesort

pt - kill � match - command Query � match - state "Sorting result" busy - time 5 � host � port � interval � print � kill � victims all

Kill sql to tmp table

pt-kill by ES321en-ES323en by ES324en-ES325en "Copying tmp" busy-ES331en 5 by host interval by interval By kill by all by ES324en-ES329en