Percona-toolkit Pt-kill Kill the Mysql query or connection method _mysql

Pt-kill is a very simple tool to kill MySQL threads and queries. The main purpose is to prevent some long queries from consuming system resources for long periods of time, while affecting the online business.

Main function:

Get the 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.
Our main use here is to prevent some select operations from being too long, which can affect other online SQL.

Installation:

Install Percona-toolkit can

Use Example:

Pt-kill--log-dsn d=testdb,t=kill_log--create-log-table--host=host2--user=root- busy-time=10--print--kill-query--match-info "Select|select"--victims All

You can also use--config to write a profile:

Pt-kill--config tmp.txt--log-dsn d=testdb,t=kill_log--create-log-table--match-info "Select|select" all
NOTES:--match-info is case-sensitive, matching a select means you cannot match a select, so use "select|select" to contain the case
Cat Tmp.txt
Host=host2
User=root
Password=root
port=3306
busy-time=10
Print
Kill-query

The role of the use paradigm:

If no Testdb.kill_log table exists, the table is created and all Pt-kill actions are logged to the table. Print displays for all SELECT statements that have a query time of more than 10 seconds, and it will kill this query.
Pt-kill Default check interval is 5 seconds

Explain:

--log-dsn D=testdb,t=kill_log--create-log-table is to create testdb.kill_log tables, and then log records of Pt-kill operations in the table
--busy-time=10 execution time of more than 10 seconds
The--print--kill-query action is for print and kill query, except for the kill connection:--kill
--match-info ' Select|select ' matches only SELECT statements

Some other important parameters are explained:

--interval to run the check query interval,
--victim
Oldest (default), kill only the oldest query (first initiated). 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
All-but-oldest kill all but the longest keep not kill
--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.

The next set of parameters is the ignore or match for the corresponding column in show Processlist:

--ignore-command/--match-command
(Command: Query, sleep, Binlog Dump, Connect, delayed Insert, Execute, Fetch, Init DB, Kill, Prepare, Processlist, Quit, Reset stmt, Table Dump)
--IGNORE-DB/--match-db
--ignore-host/--match-host
--ignore-info/--match-info
(info can be matched with SELECT, UPDATE, INSERT, delete, and you can use the ' | ' Perform multiple matches, such as "select| select|delete| delete|update| UPDATE "
--ignore-state/--match-state
(State types are: Locked, login, copy to TMP table, copying to TMP table, copying to TMP table on disk, creating TMP table, executing, Re Ading from net, sending data, sorting for order, sorting result, Table lock, updating)
--ignore-user/--match-user
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

Usage feeling:

Originally wanted to use this tool to monitor multiple MySQL instances, but found that--host can only fill in one, write more did not work. So it's a bit inconvenient that this tool can only monitor a MySQL instance after a process.  Personal feeling should have a way to achieve multiple instance monitoring and killing. Here is not done in-depth research, there are friends who know, please leave a message oh.