MySQL connection thread kill weapon Pt-kill

How do I check every 10 seconds to kill a query that exceeds 100 seconds for a specified user?

Pt-kill--no-version-check--host 127.0.0.1--port 3306--user ' xxxxxx '--password ' xxxxxx '--charset UTF8--match-command Query--match-user The specified user name--busy-time--kill--victims all--interval--print

Common parameter Description

• No-version-check
  Not up-to-date check version
• Host
  The address of the connection database
• Port
  Port connecting to the database
• User
  User name of the connection database
• Passowrd
  Password to connect to the database
• CharSet
  Specifying a character Set
• Match-command
  Specify the type of query to kill
• Match-user
  Specifies the user name to kill, which is the query that kills the user
• Busy-time
  Specify a query that kills more than a few seconds
• Kill
  Execute KILL command
• Victims
  Represents a selection from a matching result, similar to the where part of SQL, all is a query
• Interal
  Check once every few seconds
• Print
  Print out the kill query.

Pt-kill Common Kill process parameters:

1. Press User Kill

/usr/bin/pt-kill--busy-time--match-user= "dbUSER1 | DbUSER2,... "--victim all--interval 1--kill--daemonize--pid=/tmp/ptkill.pid--print--log=/home/pt-kill.log

Note: Test by user to kill thread, note--match-user between multiple users | Separated.

2. Press query to source host kill

/usr/bin/pt-kill--busy-time--match-host= "192.168.10.10 | 192.168.10.11 "--victim all--interval 1--kill--daemonize--pid=/tmp/ptkill.pid--print--log=/home/pt-kill.log

Using--ignore-host or--match-host

Note: Tests are done by the source host to kill the thread, note that the--match-host is used between multiple hosts | Separated.

3. Press Command to Kill

/usr/bin/pt-kill--busy-time--match-command= "Query | Execute "--victim all--interval 1--kill--daemonize--pid=/tmp/ptkill.pid--print--log=/home/pt-kill.log

/usr/bin/pt-kill--busy-time--ignore-command= "Sleep | Binlogdump "--victim all--interval 1--kill--daemonize--pid=/tmp/ptkill.pid--print--log=/home/pt-kill.log

Note: Test by command to kill the thread, note that command content must strictly match the case, otherwise it will not kill. Note--match-command multiple command-by | Separated, otherwise it will fail.
(Command: Query, Sleep, Binlog Dump, Connect, Delayed Insert, Execute, Fetch, Init DB, Kill, Prepare, Processlist, Quit, Reset stmt, Table Dump)

4. Kill by State

/usr/bin/pt-kill--busy-time--match-state= "Locked | Sending data "--victim all--interval 1--kill--daemonize--pid=/tmp/ptkill.pid--print--log=/home/pt-kill.log

Note: The test by pressing the state to kill the thread, note that the state of the content must be strictly match the case, otherwise it will not kill. Note--match-state multiple state between | Separated, otherwise it will fail.

(The state type is: 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)

5. Click the info keyword to kill

/usr/bin/pt-kill--busy-time--match-info= "SELECT | DELETE "--victim all--interval 1--kill--daemonize--pid=/tmp/ptkill.pid--print--log=/home/pt-kill.log

Note: The test by the press info to kill the thread, note that the content of info must match the case strictly, otherwise it will not kill. Note--match-info multiple info in between | Separated, otherwise it will fail.

--ignore-info/--match-info
Info can be matched using SELECT, UPDATE, INSERT, delete, and you can use the | Make multiple matches, such as "select| select|delete| delete|update| UPDATE "

6. On-Access dbname kill

/usr/bin/pt-kill--busy-time--match-db= "DB1 | DB2 "--victim all--interval 1--kill--daemonize--pid=/tmp/ptkill.pid--print--log=/home/pt-kill.log
--IGNORE-DB/--match-db
Note: The test by the db to kill the thread, note that the contents of the DB must be strictly match the case, otherwise it will not kill. Note--match-db between multiple db with | Separated, otherwise it will fail.

Another: Actions:
--kill Kill the connection and exit
--kill-query only kills the statement that the connection executes, but the thread is not terminated

Please refer to the official website for details: https://www.percona.com/doc/percona-toolkit/2.1/pt-kill.html

MySQL connection thread kill weapon Pt-kill