暂无图片
暂无图片
1
暂无图片
暂无图片
暂无图片
首页 / pt-kill工具的使用

pt-kill工具的使用

原创 一名数据库小白的成长笔记 2024-07-06
300

pt-kill工具的使用

一 命令介绍

1.1 pt-kill

Kill MySQL queries that match certain criteria.
终止指定连接

1.2 查看help帮助命令

[root@node3 ~]# pt-kill --help
pt-kill kills MySQL connections. pt-kill connects to MySQL and gets queries from
SHOW PROCESSLIST if no FILE is given. Else, it reads queries from one or more
FILE which contains the output of SHOW PROCESSLIST. If FILE is -, pt-kill reads
from STDIN. For more details, please use the --help option, or try ‘perldoc
/root/percona-toolkit-3.6.0/bin/pt-kill’ for complete documentation.

Usage: pt-kill [OPTIONS] [DSN]

Options:

–ask-pass Prompt for a password when connecting to MySQL
–charset=s -A Default character set
–config=A Read this comma-separated list of config files; if
specified, this must be the first option on the
command line
–create-log-table Create the --log-dsn table if it does not exist
–daemonize Fork to the background and detach from the shell
–database=s -D The database to use for the connection
–defaults-file=s -F Only read mysql options from the given file
–filter=s Discard events for which this Perl code doesn’t
return true
–group-by=s Apply matches to each class of queries grouped by
this SHOW PROCESSLIST column
–help Show help and exit
–host=s -h Connect to host (default localhost)
–interval=m How often to check for queries to kill. Optional
suffix s=seconds, m=minutes, h=hours, d=days; if no
suffix, s is used.
–json Prints killed queries as JSON, must be used with –
print
–json-fields=s Specify a list of additional key:value pairs to
include in JSON output when using --json, the value
of this parameter must be specified in the format of:
–kill-busy-commands=s group: Actions (default Query)
–log=s Print all output to this file when daemonized
–log-dsn=d Store each query killed in this DSN
–password=s -p Password to use when connecting
–pid=s Create the given PID file
–port=i -P Port number to use for connection
–query-id Prints an ID of the query that was just killed
–rds Denotes the instance in question is on Amazon RDS
–run-time=m How long to run before exiting. Optional suffix s=
seconds, m=minutes, h=hours, d=days; if no suffix, s
is used.
–sentinel=s Exit if this file exists (default /tmp/pt-kill-
sentinel)
–set-vars=A Set the MySQL variables in this comma-separated list
of variable=value pairs
–slave-password=s Sets the password to be used to connect to the slaves
–slave-user=s Sets the user to be used to connect to the slaves
–socket=s -S Socket file to use for connection
–stop Stop running instances by creating the --sentinel file
–[no]strip-comments Remove SQL comments from queries in the Info column
of the PROCESSLIST (default yes)
–user=s -u User for login if not current user
–verbose -v Print information to STDOUT about what is being done
–version Show version and exit
–[no]version-check Check for the latest version of Percona Toolkit,
MySQL, and other programs (default yes)
–victims=s Which of the matching queries in each class will be
killed (default oldest)
–wait-after-kill=m Wait after killing a query, before looking for more
to kill. Optional suffix s=seconds, m=minutes, h=
hours, d=days; if no suffix, s is used.
–wait-before-kill=m Wait before killing a query. Optional suffix s=
seconds, m=minutes, h=hours, d=days; if no suffix, s
is used.

Actions:

–execute-command=s Execute this command when a query matches
–fingerprint Modifies the --print behaviour and forces the query
fingerprint to be displayed instead of an original
query
–kill Kill the connection for matching queries
–kill-query Kill matching queries
–print Print a KILL statement for matching queries; does not
actually kill queries

二 实验部分：终止执行时间超过30秒的慢查询

案例一：pt-kill命令中对Command列为Query的操作进行终止

2.1 开启general log日志

vim /etc/my.cnf
#开启general log日志
general_log=1
general_log_file=/data/mysql/log/query.log

2.2 查看pt_user用户权限

[root@node3 bin]# mysql -upt_user -ppt_pass -P3306 -h192.168.100.55
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 8.0.35-27.1 Percona XtraDB Cluster (GPL), Release rel27, Revision 84d9464, WSREP version 26.1.4.3

Copyright © 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

pt_user@db 00:52: [(none)]> show databases;
±-------------------+
| Database |
±-------------------+
| db1 |
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
±-------------------+
6 rows in set (0.00 sec)

show grants for ‘pt_user’@’%’\G
*************************** 1. row ***************************
Grants for pt_user@%: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON . TO pt_user@%
*************************** 2. row ***************************
Grants for pt_user@%: GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ABORT_EXEMPT,AUDIT_ADMIN,AUTHENTICATION_POLICY_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FIREWALL_EXEMPT,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,GROUP_REPLICATION_STREAM,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PASSWORDLESS_USER_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SENSITIVE_VARIABLES_OBSERVER,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,TELEMETRY_LOG_ADMIN,XA_RECOVER_ADMIN ON . TO pt_user@%
2 rows in set (0.00 sec)

2.3 终止执行时间超过30秒的慢查询

具体命令如下

pt-kill h=192.168.100.55,u=pt_user,p=pt_pass,P=3306 --busy-time 30 --interval 10 --print --kill --match-info “(?i-xsm:select)”

2.4 模拟慢查询

select sleep(1000)

2.5 查看general_log日志

日志显示终止了Command为Query的进程号19

2024-07-06T17:02:28.826054Z 18 Query select sleep(1000)
2024-07-06T17:02:38.823592Z 17 Query SHOW FULL PROCESSLIST
2024-07-06T17:02:48.829087Z 17 Query SHOW FULL PROCESSLIST
2024-07-06T17:02:58.833705Z 17 Query SHOW FULL PROCESSLIST
2024-07-06T17:02:58.836946Z 17 Query KILL ‘18’
2024-07-06T17:02:58.851566Z 19 Connect pt_user@node3 on db1 using SSL/TLS
2024-07-06T17:02:58.853084Z 19 Query select sleep(1000)
2024-07-06T17:03:08.856268Z 17 Query SHOW FULL PROCESSLIST
2024-07-06T17:03:18.864543Z 17 Query SHOW FULL PROCESSLIST
2024-07-06T17:03:28.878991Z 17 Query SHOW FULL PROCESSLIST
2024-07-06T17:03:28.881816Z 17 Query KILL '19’
2024-07-06T17:03:38.884463Z 17 Query SHOW FULL PROCESSLIST
2024-07-06T17:03:48.897437Z 17 Query SHOW FULL PROCESSLIST
2024-07-06T17:03:58.912519Z 17 Query SHOW FULL PROCESSLIST

案例二：pt-kill命令中对Command列为Sleep列的操作进行终止

3.1 创建用户u1，赋权，验证远程登录

[root@node3 ~]# mysql -uroot -p123456

root@db 23:37: [test]> create user ‘u1’@’%’ identified with mysql_native_password by ‘123456’;

GRANT all privileges ON . TO ‘u1’@’%’;

flush privileges;

show grants for ‘u1’@’%’\G

验证远程登陆是否成功:

[root@node3 ~]# mysql -uu1 -p123456 -h192.168.100.55 -P3306

3.2 查看连接connection_id

select connection_id();

u1@db 01:20: [(none)]> select connection_id();
±----------------+
| connection_id() |
±----------------+
| 21 |
±----------------+
1 row in set (0.00 sec)

3.3 模拟数据库插入100000条数据

set global innodb_flush_log_at_trx_commit=0;
create database db1 charset=utf8;
use db1;
create table t(id int primary key, a int, b int, index(a)) engine=innodb;
delimiter ;;
create procedure idata()
begin
declare i int;
set i=1;
while(i<=1000000) do
insert into t values(i,i,i);
set i=i+1;
end while;
end;;
delimiter ;
call idata();

3.4 终止用户u1执行时间超过30秒的操作

pt-kill h=192.168.100.55,u=u1,p=123456,P=3306 --busy-time 30 --interval 10 --print --kill --match-user u1

执行pt-kill命令
终端输出日志：
[root@node3 ~]# pt-kill h=192.168.100.55,u=u1,p=123456,P=3306 --busy-time 30 --interval 10 --print --kill --match-user u1

2024-07-07T01:24:15 KILL 21 (Sleep 3 sec) NULL

3.5 查看general_log日志

cd /data/mysql/log
tail-20f query.log

024-07-06T17:22:17.916465Z 21 Query select connection_id()
2024-07-06T17:23:02.860342Z 21 Query set global innodb_flush_log_at_trx_commit=0
2024-07-06T17:23:02.885883Z 21 Query create database db1 charset=utf8
2024-07-06T17:23:02.969921Z 21 Query SELECT DATABASE()
2024-07-06T17:23:02.970476Z 21 Init DB db1
2024-07-06T17:23:02.983894Z 21 Query create table t(id int primary key, a int, b int, index(a)) engine=innodb
2024-07-06T17:23:03.132359Z 21 Query create procedure idata()
begin
declare i int;
set i=1;
while(i<=1000000) do
insert into t values(i,i,i);
set i=i+1;
end while;
end
2024-07-06T17:23:04.260379Z 21 Query call idata()
2024-07-06T17:23:35.857184Z 22 Connect u1@node3 on using TCP/IP
2024-07-06T17:23:35.860832Z 22 Query SELECT VERSION()
2024-07-06T17:23:35.862569Z 22 Query SHOW VARIABLES LIKE ‘character_set_server’
2024-07-06T17:23:35.900807Z 22 Query SET NAMES ‘utf8mb4’
2024-07-06T17:23:35.901588Z 22 Query SHOW VARIABLES LIKE ‘wait_timeout’
2024-07-06T17:23:35.906702Z 22 Query SET SESSION wait_timeout=10000
2024-07-06T17:23:35.908294Z 22 Query SELECT @@SQL_MODE
2024-07-06T17:23:35.909989Z 22 Query SET @@SQL_QUOTE_SHOW_CREATE = 1/!40101, @@SQL_MODE=‘NO_AUTO_VALUE_ON_ZERO,NO_UNSIGNED_SUBTRACTION,NO_ENGINE_SUBSTITUTION’/
2024-07-06T17:23:35.912065Z 22 Query SELECT @@server_id /!50038 , @@hostname/, CONNECTION_ID() as connection_id
2024-07-06T17:23:35.914171Z 22 Query SELECT CONCAT(@@hostname, @@port)
2024-07-06T17:23:35.915890Z 22 Query SHOW FULL PROCESSLIST
2024-07-06T17:23:45.917204Z 22 Query SHOW FULL PROCESSLIST
2024-07-06T17:23:55.918579Z 22 Query SHOW FULL PROCESSLIST
2024-07-06T17:24:05.920088Z 22 Query SHOW FULL PROCESSLIST
2024-07-06T17:24:15.934795Z 22 Query SHOW FULL PROCESSLIST
2024-07-06T17:24:15.935824Z 22 Query KILL ‘21’
2024-07-06T17:24:25.950894Z 22 Query SHOW FULL PROCESSLIST

3.6 pt-kill终止command列为Sleep的操作，而不是command列为Query的操作

session1：
u1@db 01:34: [(none)]> select connection_id();
±----------------+
| connection_id() |
±----------------+
| 37 |
±----------------+
1 row in set (0.01 sec)

session2：
root@db 01:35: [(none)]> show full processlist;
±—±----------------±------------±-----±--------±-----±-----------------------±----------------------±--------±----------±--------------+
| Id | User | Host | db | Command | Time | State | Info | Time_ms | Rows_sent | Rows_examined |
±—±----------------±------------±-----±--------±-----±-----------------------±----------------------±--------±----------±--------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 4621 | Waiting on empty queue | NULL | 4620453 | 0 | 0 |
| 33 | u1 | node3:40621 | NULL | Sleep | 6 | | NULL | 5611 | 0 | 0 |
| 35 | root | localhost | NULL | Query | 0 | init | show full processlist | 0 | 0 | 0 |
| 37 | u1 | node3:40627 | NULL | Sleep | 4 | | NULL | 3402 | 1 | 1 |
±—±----------------±------------±-----±--------±-----±-----------------------±----------------------±--------±----------±--------------+
4 rows in set, 1 warning (0.00 sec)

session3：

[root@node3 ~]# pt-kill h=192.168.100.55,u=u1,p=123456,P=3306 --busy-time 30 --interval 10 --print --kill --match-user u1

2024-07-07T01:24:15 KILL 21 (Sleep 3 sec) NULL

2024-07-07T01:27:36 KILL 23 (Sleep 3 sec) NULL

2024-07-07T01:28:46 KILL 24 (Sleep 10 sec) NULL

2024-07-07T01:29:36 KILL 26 (Sleep 3 sec) NULL

2024-07-07T01:30:36 KILL 27 (Sleep 4 sec) NULL

2024-07-07T01:30:46 KILL 28 (Sleep 8 sec) NULL

2024-07-07T01:31:16 KILL 29 (Sleep 3 sec) NULL

2024-07-07T01:32:16 KILL 30 (Sleep 3 sec) NULL

2024-07-07T01:33:59 KILL 34 (Sleep 8 sec) NULL

2024-07-07T01:34:40 KILL 36 (Sleep 5 sec) NULL

2024-07-07T01:35:40 KILL 37 (Sleep 8 sec) NULL

这里pt-kill命令终止的会话进程号为37，Command 值为Sleep，而不是Query，所以不满足我们终止慢查询的初衷。pt-kill命令中只对Command列为Query的操作才有效果。

三 常见用法

3.1 将kill操作记录在数据库中

[root@node3 ~]# mysql -upt_user -ppt_pass -P3306 -h192.168.100.55
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 41
Server version: 8.0.35-27.1 Percona XtraDB Cluster (GPL), Release rel27, Revision 84d9464, WSREP version 26.1.4.3

Copyright © 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

pt_user@db 02:46: [(none)]> show databases;
±-------------------+
| Database |
±-------------------+
| db1 |
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
±-------------------+
6 rows in set (0.01 sec)

session1:

select sleep(1000);

session2：

pt-kill h=192.168.100.55,u=pt_user,p=pt_pass,P=3306 --busy-time 30 --interval 10 --print --kill --log-dsn h=192.168.100.55,u=pt_user,p=pt_pass,P=3306,D=db1,t=kill_log --create-log-table

pt_user@db 02:55: [db1]> show tables;
±--------------+
| Tables_in_db1 |
±--------------+
| kill_log |
±--------------+
1 row in set (0.00 sec)

pt_user@db 02:56: [db1]> desc kill_log;
±-----------±-------------±-----±----±--------±---------------+
| Field | Type | Null | Key | Default | Extra |
±-----------±-------------±-----±----±--------±---------------+
| kill_id | int unsigned | NO | PRI | NULL | auto_increment |
| server_id | bigint | NO | | 0 | |
| timestamp | datetime | YES | | NULL | |
| reason | text | YES | | NULL | |
| kill_error | text | YES | | NULL | |
| Id | bigint | NO | | 0 | |
| User | varchar(16) | NO | | | |
| Host | varchar(64) | NO | | | |
| db | varchar(64) | YES | | NULL | |
| Command | varchar(16) | NO | | | |
| Time | int | NO | | 0 | |
| State | varchar(64) | YES | | NULL | |
| Info | longtext | YES | | NULL | |
| Time_ms | bigint | YES | | 0 | |
±-----------±-------------±-----±----±--------±---------------+
14 rows in set (0.00 sec)

pt_user@db 02:55: [db1]> select * from kill_log;
±--------±----------±--------------------±------------------±-----------±—±--------±------------±-----±--------±-----±-----------±-------------------±--------+
| kill_id | server_id | timestamp | reason | kill_error | Id | User | Host | db | Command | Time | State | Info | Time_ms |
±--------±----------±--------------------±------------------±-----------±—±--------±------------±-----±--------±-----±-----------±-------------------±--------+
| 1 | 553306 | 2024-07-07 02:54:08 | Exceeds busy time | | 41 | pt_user | node3:40633 | NULL | Query | 142 | User sleep | select sleep(1000) | 142289 |
| 2 | 553306 | 2024-07-07 02:54:38 | Exceeds busy time | | 51 | pt_user | node3:40653 | NULL | Query | 30 | User sleep | select sleep(1000) | 30041 |
| 3 | 553306 | 2024-07-07 02:55:08 | Exceeds busy time | | 52 | pt_user | node3:40655 | NULL | Query | 30 | User sleep | select sleep(1000) | 30019 |
±--------±----------±--------------------±------------------±-----------±—±--------±------------±-----±--------±-----±-----------±-------------------±--------+
3 rows in set (0.00 sec)

3.2 将pt-kill作为守护进程运行

pt-kill h=192.168.100.55,u=pt_user,p=pt_pass,P=3306 --busy-time 30 --interval 10 --print --kill --log /tmp/pt-kill.log --daemonize

[root@node3 ~]# ps -ef|grep pt-kill|grep -v grep
root 9216 1 0 02:59 ? 00:00:00 perl /root/percona-toolkit-3.6.0/bin/pt-kill h=192.168.100.55,u=pt_user,p=pt_pass,P=3306 --busy-time 30 --interval 10 --print --kill --log /tmp/pt-kill.log --daemonize

墨力计划mysql
最后修改时间：2024-07-08 10:14:20
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论

相关阅读

MySQL8.0统计信息总结
闫建(Rock Yan)
559次阅读
2025-03-17 16:04:03
MySQL生产实战优化（利用Index skip scan优化性能提升257倍）
chengang
378次阅读
2025-03-17 10:36:40
MySQL数据库当前和历史事务分析
听见风的声音
353次阅读
2025-04-01 08:47:17
墨天轮个人数说知识点合集
JiekeXu
332次阅读
2025-04-01 15:56:03
MySQL 生产实践-Update 二级索引导致的性能问题排查
chengang
322次阅读
2025-03-28 16:28:31
MySQL8.0直方图功能简介
Rock Yan
260次阅读
2025-03-21 15:30:53
MySQL 有没有类似 Oracle 的索引监控功能？
JiekeXu
244次阅读
2025-03-19 23:43:22
云和恩墨杨明翰：安全生产系列之MySQL高危操作
墨天轮编辑部
238次阅读
2025-03-27 16:45:26
openHalo问世，全球首款基于PostgreSQL兼容MySQL协议的国产开源数据库
严少安
228次阅读
2025-04-07 12:14:29
使用Veridata 23c进行异构数据库的数据校验
曹海峰
165次阅读
2025-03-14 13:11:42
目录
  • pt-kill工具的使用
    • 一 命令介绍
      • 1.1 pt-kill
      • 1.2 查看help帮助命令
    • 二 实验部分：终止执行时间超过30秒的慢查询
      • 案例一：pt-kill命令中对Command列为Query的操作进行终止
      • 2.1 开启general log日志
      • 2.2 查看pt_user用户权限
      • 2.3 终止执行时间超过30秒的慢查询
      • 2.4 模拟慢查询
      • 2.5 查看general_log日志
      • 案例二：pt-kill命令中对Command列为Sleep列的操作进行终止
      • 3.1 创建用户u1，赋权，验证远程登录
      • 3.2 查看连接connection_id
      • 3.3 模拟数据库插入100000条数据
      • 3.4 终止用户u1执行时间超过30秒的操作
      • 3.5 查看general_log日志
      • 3.6 pt-kill终止command列为Sleep的操作，而不是command列为Query的操作
    • 三 常见用法
      • 3.1 将kill操作记录在数据库中
      • 3.2 将pt-kill作为守护进程运行