Percona utility-Introduction to pt-kill, perconapt-kill

Percona utility-Introduction to pt-kill, perconapt-kill

Pt-kill is an excellent tool for kill MySQL connection and is part of percona toolkit, when the maximum number of connections is exceeded due to a large number of idle connections and a problematic SQL statement causes a high mysql load, you must kill some connections. This tool is mainly used for this purpose.

This tool is highly practical at work to obtain matching connections from show processlist or read matching connections from files containing show processlist and print or kill or execute other operations, when the server connection encounters an exception, the first thought is pt-kill. You can write a script yourself, but the function is still not powerful with pt-kill; generally, I like to put necessary information for connecting to local MySQL in a file. When you need to connect to local mysql, you can directly specify the configuration file;

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

Pt-kill defaults-file xx -- busy-time 60 -- kill -- victims all -- interval 10 the number of connections whose initial runnning State exceeds 60 s at every 10 s;

Several important parameters
-- Daemonize runs in the background as a daemprocess;
-- How often does interval run? The unit can be s, m, h, d. The default value is s.
-- Victims uses oldest by default and only kills the oldest query. This is to prevent the virus from being scanned and killed for long-running queries. They are only waiting for a long time. This type of match is queried by time, killing the maximum time.
All: Kill all satisfied threads.
Killed all, but the longest retained does not kill

Action
-- Kill the connection and exit
-- Kill-query: only the statement executed by the connection is killed, but the thread is not terminated.
-- Print the statement that meets the conditions

QUERY MATCHES
-- Busy-time: batch query of threads whose running time exceeds this time;
-- Idle-time: the connection thread for killing sleep time must be valid only when -- match-command sleep

For more information, see the official documentation: https://www.percona.com/doc/percona-toolkit/2.1/pt-kill.html#cmdoption-pt-kill--interval

Parameters

-Busy-time

Running time

-Idle-time

Idle Time

-Victims

All matched connections should have the longest connection

-Interval

Interval. The default value is 30 s. It is a bit long and can be adjusted according to the actual situation.

-Print

Print the kill connection.

-Match-command

Match the current connection command

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

Match the current connection status

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 a regular expression to match the correct SQL statement

-Match-db-match-user-match-host

Mingzhiyi

Common usage

Kill idle links

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

Kill the link that has been running for more than 5s

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

Kill running SQL statements that match a rule.

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

Kill the SQL statement that is executing filesort

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

Kill the SQL statement that is being Copying to tmp table

Pt-kill-match-command Query-match-state "Copying to tmp table" busy-time 5-host-port-interval-print-kill-victims all