Percona-toolkit Pt-kill Kill the Mysql query or connection method _mysql

Source: Internet
Author: User
Tags dsn mysql query print print percona

Pt-kill is a very simple tool to kill MySQL threads and queries. The main purpose is to prevent some long queries from consuming system resources for long periods of time, while affecting the online business.

Main function:

Get the connection that satisfies the condition from show processlist or read the connection that satisfies the condition from the file containing show processlist and print or kill or perform other actions.
Our main use here is to prevent some select operations from being too long, which can affect other online SQL.

Installation:

Install Percona-toolkit can

Use Example:

Pt-kill--log-dsn d=testdb,t=kill_log--create-log-table--host=host2--user=root- busy-time=10--print--kill-query--match-info "Select|select"--victims All

You can also use--config to write a profile:

Pt-kill--config tmp.txt--log-dsn d=testdb,t=kill_log--create-log-table--match-info "Select|select" all
NOTES:--match-info is case-sensitive, matching a select means you cannot match a select, so use "select|select" to contain the case
Cat Tmp.txt
Host=host2
User=root
Password=root
port=3306
busy-time=10
Print
Kill-query

The role of the use paradigm:

If no Testdb.kill_log table exists, the table is created and all Pt-kill actions are logged to the table. Print displays for all SELECT statements that have a query time of more than 10 seconds, and it will kill this query.
Pt-kill Default check interval is 5 seconds

Explain:

--log-dsn D=testdb,t=kill_log--create-log-table is to create testdb.kill_log tables, and then log records of Pt-kill operations in the table
--busy-time=10 execution time of more than 10 seconds
The--print--kill-query action is for print and kill query, except for the kill connection:--kill
--match-info ' Select|select ' matches only SELECT statements

Some other important parameters are explained:

--interval to run the check query interval,
--victim
Oldest (default), kill only the oldest query (first initiated). This is not really a long run of inquiries to prevent being killed, they just wait for a long time. This kind of match is queried by time, killing a maximum of time.
All kill all the satisfied threads
All-but-oldest kill all but the longest keep not kill
--busy-time Batch query has been running longer than this time thread;
--idle-time kill how many hours of sleep the connection thread must be active at--match-command.

The next set of parameters is the ignore or match for the corresponding column in show Processlist:

--ignore-command/--match-command
(Command: 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 can be matched with SELECT, UPDATE, INSERT, delete, and you can use the ' | ' Perform multiple matches, such as "select| select|delete| delete|update| UPDATE "
--ignore-state/--match-state
(State types are: 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)
--ignore-user/--match-user
Action:
--kill Kill the connection and exit.
--kill-query only kills the statement that the connection executes, but the thread is not terminated
--print Print statements that meet the criteria

Usage feeling:

Originally wanted to use this tool to monitor multiple MySQL instances, but found that--host can only fill in one, write more did not work. So it's a bit inconvenient that this tool can only monitor a MySQL instance after a process.  Personal feeling should have a way to achieve multiple instance monitoring and killing. Here is not done in-depth research, there are friends who know, please leave a message oh.

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.