percona toolkit pt ES3en kill mysql query or join method

pt-kill is a very simple tool to kill mysql threads and queries. Mainly in order to prevent 1 some long query long time occupying system resources, and the impact on the online business.

Main Functions:

Retrieves the qualified connection from show processlist or reads the qualified connection from the file containing show processlist and prints or kills or performs other operations.
The main purpose here is to prevent some select operations from taking too long and affecting other online SQL.

Installation:

Just install ES19en-ES20en

Examples of use:

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 write configuration files using --config:

pt-kill --config tmp.txt --log-dsn D=testdb,t=kill_log --create-log-table --match-info "SELECT|select" --victims all
notes: -- match-info is case sensitive. Matching SELECT means there is no match for select, so using "SELECT|select" contains case
cat tmp.txt
host=host2
user=root
password=root
port=3306
busy-time=10
print
kill-query

The role of the use example:

If the ES60en.es61EN_log table does not exist, the table is created and all pt-ES64en operations are logged into the table. print is displayed for all SELECT statements whose query time exceeds 10 seconds, and kill is displayed for query.
pt-kill default check interval is 5 seconds

Explanation:

-- ES76en-ES78en =testdb,t=kill_log -- ES83en-ES84en-ES85en creates the table testdb.kill_ES88en and logs the operation of ES89en-ES90en in the table
--busy-time=10 for execution time exceeding 10 seconds
The action is performed by print and kill query, in addition to the action there is kill connection: --kill
-- match-info 'SELECT|select' matches only the SELECT statement

Some other important parameters:

--interval run check the interval of query,
--victim
oldest (default), which kills only the oldest queries (those initiated first). 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
all-but-oldest kills all, but does not kill the longest retention
-- Es126EN-ES127en batch query threads that have been running for longer than this time;
-- idle-ES130en Kill sleep how much time connection thread, must be in -- ES132en-ES133en sleep to be effective

The next set of parameters is for the neglect or matching of the corresponding column in show processlist:

--ignore-command / --match-command
(command, Query, Binlog, Dump, Connect, Delayed insert, Execute, Fetch DB, Kill, Delayed DB, Kill Fetch, Kill Fetch, Kill Fetch, Reset DB, Kill Dump)
--ignore-db / --match-db
--ignore-host / --match-host
--ignore-info / --match-info
(info can use select, update, insert, delete for matching, and can use "|" for multiple matching, such as "select|SELECT|delete|DELETE|update|UPDATE"
--ignore-state / --match-state
(state types are: Locked, login, copy tmp table, Copying to table, to table table, to table, Locked login table, to table to to table, Locked login table table table to to table data, Sorting for order, Sorting result, Table lock
--ignore-user / --match-user
Action:
--kill kills the connection and exits
-- ES225en-ES226en only kills the statement executed by the connection, but the thread is not terminated
--print prints statements that meet the criteria

Usage experience:

I intended to use this tool to monitor multiple INSTANCES of mysql, but I found that --host could only fill in one instance, and it didn't work if I wrote more. So this tool can only monitor 1 instance of mysql after 1 process, which is a little inconvenient. Personally, I feel that there should be a way to achieve multi-instance monitoring and killing. Here did not do in-depth research, know friends, please leave a message oh.