MySQL connection thread kill weapon Pt-kill

Source: Internet
Author: User
Tags percona

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.