Use pt-kill to kill the connection method according to certain rules, pt-killkill

Use pt-kill to kill the connection method according to certain rules, pt-killkill

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.

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