Percona-toolkit pt-kill method for killing mysql queries or connections _ MySQL

This article describes the use of pt-kill in percona-toolkit and some important parameters, for more information, see pt-kill. it is a simple tool to kill mysql threads and query. It is mainly used to prevent long queries from occupying system resources for a long period of time, which affects online services.

Main functions:

Obtain a connection that meets the conditions from show processlist or read the connection that meets the conditions from the file that contains show processlist, print or kill the connection, or perform other operations.
Here we mainly prevent some select operations from taking too long to affect other online SQL statements.

Installation:

Install percona-toolkit.

Example:

pt-kill --log-dsn D=testdb,t=kill_log --create-log-table --host=host2 --user=root --password=root --port=3306 --busy-time=10 --print --kill-query --match-info "SELECT|select"  --victims all

You can also use -- config to write the configuration file:

Pt-kill -- config tmp.txt -- log-dsn D = testdb, t = kill_log -- create-log-table -- match-info "SELECT | select" -- victims allnotes: -- match-info is case-sensitive. matching SELECT means that the select statement cannot be matched, therefore, use "SELECT | select" to include the case cat tmp.txt host = host2user = rootpassword = rootport = 3306busy-time = 10 print kill-query

The role of this example:

If the testdb. kill_log table does not exist, create the table and record all pt-kill operations in the table. Print all SELECT statements whose query time exceeds 10 seconds and kill the query.
The default check interval of pt-kill is 5 seconds.

Explanation:

-- Log-dsn D = testdb, t = kill_log -- create-log-table is used to create testdb. in the kill_log table, the pt-kill operation logs are then recorded in the table -- busy-time = 10 -- print -- kill-query action that has been executed for more than 10 seconds is print and kill query, the other actions include the kill connection: -- kill -- match-info 'Select | Select'. only SELECT statements are matched.

Other important parameter explanations:

-- Interval: interval between query checks,
-- Victim
Oldest (default): only the oldest query (first initiated) is killed ). 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.
All-but-oldest kills all, but the longest retained does not kill
-- 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

The following parameters are used to ignore or match the corresponding columns in show processlist:

-- Ignore-command/-- match-command (commands include: 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, select, update, insert, and delete can be used. match, you can also use "|" to perform multiple matching, for example, "select | SELECT | delete | DELETE | update | UPDATE" -- ignore-state/-- match-state (the state types include: 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) -- ignore-user/-- match-userAction: -- kill kills the connection and exits -- kill-query only kills the statement executed by the connection, but the thread is not terminated. -- print prints the statement that meets the conditions.

Experience:

I originally wanted to use this tool to monitor multiple mysql instances, but I found that only one host can be filled in, and it does not work if I write more. Therefore, this tool can only monitor one mysql instance after starting a process, which is not very convenient. I personally feel that there should be a way to achieve multi-instance monitoring and detection and removal. I haven't done any further research here. if you know anything, please leave a message.

The above is the pt-kill method of percona-toolkit to kill mysql Query or connection _ MySQL content. For more information, see PHP Chinese network (www.php1.cn )!