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