pt-kill 使用教程

介绍

pt-kill 是一个优秀的kill MySQL连接的工具,是percona toolkit的一部分,使用perl语言编写.简单高效.能够按照一定的规则匹配kill掉MySQL中的连接,达到有效保护服务的目的.

用途

pt-kill主要适用于MySQL实例中异常出现的会话导致整个实例负载过高,甚至出现不可用的情况下,进而影响应用,影响业务的场景.包括以下:

  • 手动维护MySQL连接,比如连接突然飙高
  • 自动维护MySQL连接,为了减小对业务的影响,结合定时任务,自动检测匹配超过一定阈值时间的查询,kill掉,保护实例的正常运行

原理

pt-kill的工作原理是连接到MySQL并从show processlist获取查询并进行过滤,然后执行kill或print操作

general_log

通过开启mysql的general_log,观察pt-kill执行的语句:

1
2
3
4
5
6
7
8
2018-07-16T08:25:26.764023+08:00	  412 Connect	root@localhost on  using TCP/IP
2018-07-16T08:25:26.839130+08:00 412 Query SHOW VARIABLES LIKE 'wait\_timeout'
2018-07-16T08:25:26.936724+08:00 412 Query SET SESSION wait_timeout=10000
2018-07-16T08:25:26.937101+08:00 412 Query SELECT @@SQL_MODE
2018-07-16T08:25:26.937323+08:00 412 Query SET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'*/
2018-07-16T08:25:26.937534+08:00 412 Query SELECT @@server_id /*!50038 , @@hostname*/
2018-07-16T08:25:26.938054+08:00 412 Query SHOW FULL PROCESSLIST
2018-07-16T08:25:27.938917+08:00 412 Query SHOW FULL PROCESSLIST

用法

pt-kill [OPTIONS] [DSN]

pt-kill杀死MySQL连接。如果没有给出文件,pt-kill连接到MySQL然后从”SHOW PROCESSLIST”命令输出中获取查询。否则,就从包含有”SHOW PROCESSLIST”输出的一个或者多个文件中读取查询。如果文件是” - “,pt-kill从STDIN读取输入。

1.kill执行时间超过60s的查询

1
pt-kill --busy-time 60 --kill

2.条件同上,但是只print不kill

1
pt-kill --busy-time 60 --print

3.每10s检查sleep进程并kill

1
pt-kill --match-command Sleep --kill --victims all --interval 10

4.打印所有的登录进程

1
pt-kill --match-state login --print --victims all

5.查看当前进程列表中的查询匹配

1
2
mysql -e "SHOW PROCESSLIST" > proclist.txt
pt-kill --test-matching proclist.txt --busy-time 60 --print

风险

Percona Toolkit已经成熟,并得到了验证,并经过了充分测试,但所有数据库工具都可能对系统和数据库服务器造成风险,在使用此工具前注意:

  • 阅读工具的文档
  • 查看工具已知的bug列表
  • 在非生产服务器上测试
  • 备份生产服务器并验证备份有效性

描述

pt-kill从”SHOW PROCESSLIST”中捕捉查询,筛选,然后kill或者print,被称为“慢查询狙击手”。

pt-kill一般连接到MySQL从”SHOW PROCESSLIST”里获取查询。或者也可以从文件里面读取”SHOW PROCESSLIST”的输出结果。在这种情况下,pt-kill不会连接到MySQL,”–kill”选项也不会起作用。当读取文件时需要用”–print”选项,通过结合”–test-matching”选项读取文件,进行测试,为了保证匹配规则的正确性。特殊情况下要考虑,例如“不kill复制线程”等。

其中,选项–busy-time和–victims比较重要。

  • 首先,从”SHOW PROCESSLIST”里匹配对应的结果,比如–match-command规则去匹配command值, –busy-time去匹配time时间
  • 其次, –victims控制从每个类别里面匹配的哪些查询会被kill,默认最长时间的查询优先被kill

参数

至少需要指定一个–kill,–kill-query,–print,–execute-command和–stop,其中以下参数是互斥的

  • –any-busy-time和–each-busy-time
  • –kill和–kill-query
  • –daemonize和–test-matching

option选项

参数 描述
–ask-pass 连接MySQL时提示输入密码
–charset 默认字符集
–config 指定配置配置文件
–create-log-table 如果表–log-dsn不存在,则创建此表
–daemonize 后台进行进程
–database 连接的数据库
–defaults-file 从指定的文件读取mysql参数
–filter 丢弃Perl代码不返回true的事件
–group-by 将show processlist的匹配分组, 还可以匹配fingerprint查询,通过该查询对Info列中的SQL查询进行抽象
–help 显示帮助
–host 连接的主机
–interval 匹配查询的频繁,如果busy-time未指定,默认为30s
–log 守护进程日志输出文件
–log-dsn 将kill的结果存储到表中,传入的dsn必须包括库和表选项,表结构如下,也适用于–create-log-table选项
CREATE TABLE kill_log (
kill_id int(10) unsigned NOT NULL AUTO_INCREMENT,
server_id bigint(4) NOT NULL DEFAULT ‘0’,
timestamp DATETIME,
reason TEXT,
kill_error TEXT,
Id bigint(4) NOT NULL DEFAULT ‘0’,
User varchar(16) NOT NULL DEFAULT ‘’,
Host varchar(64) NOT NULL DEFAULT ‘’,
db varchar(64) DEFAULT NULL,
Command varchar(16) NOT NULL DEFAULT ‘’,
Time int(7) NOT NULL DEFAULT ‘0’,
State varchar(64) DEFAULT NULL,
Info longtext,
Time_ms bigint(21) DEFAULT ‘0’, # NOTE, TODO: currently not used
PRIMARY KEY (kill_id)
) DEFAULT CHARSET=utf8
–password 连接的密码
–pid 连接的pid
–port 连接的端口
–query-id 打印被kill的query id
–rds 连接到aws rds,rds不能使用–kill和–kill-query选项,改为用函数调用.
–kill使用CALL mysql.rds_kill(thread-id)
–kill-query使用CALL mysql.rds_kill_query(thread-id)
–run-time 退出前要运行多长时间,默认pt-kill会一直运行,直到进程被创建的–sentinel文件而触发终止
–sentinel 如果此文件存在,则pt-kill进程退出
–slave-user 连接到从库的用户
–slave-password 连接到从库的密码
–set-vars 设置mysql的变量,多个变量用逗号分割
–socket 连接的socket
–stop 通过创建–sentinel文件停止运行
–[no]strip-comments 从info查询中删除sql注释
–user 连接的用户名
–version 显示版本
–[no]version-check 检查Toolkit,MySQL和其他程序的最新版本
–victims 控制从每个类别里面匹配的哪些查询会被kill,包括以下:
Oldest:只kil最长的查询,防止误kill
All:kill所有
All-but-oldest:除最长的查询外,kill 所有
–wait-after-kill Kill一个查询后等待时长
–wait-before-kill 在终止查询之前等待时长

过滤匹配

这些选项用于过滤匹配查询规则,如果查询不匹配,不会将其删除,如果使用ignore选项,其有最高优先级,默认所有的匹配是区分大小写的,可以通过正则表达式不区分大小写(?i-xsm:select)

参数 描述
–busy-time 匹配运行超过此值的查询,此时command=query时生效
–idle-time Sleep超过此值的查询,此时command=sleep时生效
–ignore-command 忽略command的perl正则表达式的查询
–ignore-db 忽略db的perl正则表达式的查询
–ignore-host 忽略host的perl正则表达式的查询
–ignore-info 忽略info的perl正则表达式的查询
–[no]ignore-self 不kill pt-kill自己的连接,默认为yes
–ignore-state 忽略state的perl正则表达式的查询
–ignore-user 忽略user的perl正则表达式的查询
–match-all 匹配所有未ignore的查询,如未指定ignore,则所有的查询都匹配
–match-command 匹配command的perl正则表达式的查询,常用command包括:
Query
Sleep
Binlog Dump
Connect
Delayed insert
Execute
Fetch
Init DB
Kill
Prepare
Processlist
Quit
Reset stmt
Table Dump
–match-db 匹配db的perl正则表达式的查询
–match-host 匹配host的perl正则表达式的查询,也支持host:port模式
–match-info 匹配info的perl正则表达式的查询
–match-state 匹配state的perl正则表达式的查询,常用state包括:
Locked
login
copy to tmp table
Copying to tmp table
Copying to tmp table on disk
Creating tmp table
executing
Reading from net
Sending data
Sorting for order
Sorting result
Table lock
Updating
–match-user 匹配user的perl正则表达式的查询
–replication-threads 允许匹配复制线程
–test-matching 用于测试匹配,此选项禁用了–run-time,–interval和–[no]ignore-self

匹配所有

这些匹配适用于所有查询,通过指定–group-by来创建组,如未指定,会匹配所有组

参数 描述
–any-busy-time 任何查询超过此值则匹配查询,如为10,则至少有一个查询时长超过10,才会匹配
–each-busy-time 每个查询超过些值则匹配查询,如为10,则只有在每个查询时长超过10,才会匹配
–query-count 至少有这么多查询才匹配
–verbose 打印正在完成的工作信息

Action

对所有的匹配查询采取的操作,默认顺序为–print,–execute-command,–kill/–kill-query.它允许–execute-command查看–print和–kill/–kill-query的输出,默认pt-kill不会传递任何信息.

参数 描述
–execute-command 查询匹配时执行此命令
–kill 终止连接并退出
–kill-busy-commands 逗号运行命令列表,如果运行超过–busy-time,会被kill,默认query
例如:–kill-busy-commands=Query,Execute
–kill-query 只杀掉连接执行的语句,线程会被终止
–print 打印匹配查询的KILL语句

DSN选项

这些选项选用于创建DSN,每个选项都给出了option=value,选项区分大小写

参数 描述
A dsn: charset; copy: yes 默认字符集
D dsn: database; copy: yes 默认数据库
F dsn: mysql_read_default_file; copy: yes 仅从指定文件读取参数
h dsn: host; copy: yes 连接的host
p dsn: password; copy: yes 连接的密码
P dsn: port; copy: yes 连接的端口
S dsn: mysql_socket; copy: yes 连接的socket
u dsn: user; copy: yes 连接的用户
t 记录操作的表,通过–log-dsn指定

案例

通过测试用例来展示pt-kill的规则及其优秀的工作能力,构造一个测试环境,使用sysbench来做mysql的压力测试,在测试过程中通过pt-kill来匹配规则并kill

测试匹配

1.查看当前进程列表添加到proclist.txt文件

1
mysql -S /opt/mysql3306/data/mysql3306.sock -e "SHOW PROCESSLIST" > proclist.txt

2.查看proclist.txt文件内容

image

3.通过pt-kill匹配测试查询

1
pt-kill --no-version-check --host 127.0.0.1 --port 3306 --user root --password 111111 --socket /opt/mysql3306/data/mysql3306.sock --test-matching proclist.txt --match-command="Query"   --victim all --interval 1 --match-state="Sending data" --print

image

可以看出测试匹配出了3个command为Query,state为Sending data的sql语句,并且kill的id与show processlist中的id对应

Host匹配查询

1.通过pt-kill连接host匹配并打印出来

1
pt-kill --no-version-check --host 127.0.0.1 --port 3306 --user root --password 111111 --socket /opt/mysql3306/data/mysql3306.sock --match-host="127.0.0.1" --victim all --interval 1 --print

image

仅匹配了host为127.0.0.1,并且未指定其他匹配,从输出上看command为Query,Sleep都打印了出来

DB匹配查询

1.通过pt-kill连接db匹配并打印出来

1
pt-kill --no-version-check --host 127.0.0.1 --port 3306 --user root --password 111111 --socket /opt/mysql3306/data/mysql3306.sock --match-db="sysbench" --victim all --interval 1 --print

image

仅匹配了db为sysbench,并且未指定其他匹配,从输出上看command为Query,Sleep都打印了出来

USER匹配查询

1.通过pt-kill连接user匹配并打印出来

1
pt-kill --no-version-check --host 127.0.0.1 --port 3306 --user root --password 111111 --socket /opt/mysql3306/data/mysql3306.sock --match-user="root" --victim all --interval 1 --print

image

仅匹配了user为root,并且未指定其他匹配,从输出上看command为Query,Sleep都打印了出来

Command匹配查询

1.通过pt-kill连接command匹配并打印出来,分别打印c
mmand只包括Sleep和包括Sleep及Query的示例

1
2
pt-kill --no-version-check --host 127.0.0.1 --port 3306 --user root --password 111111 --socket /opt/mysql3306/data/mysql3306.sock --match-command="Sleep" --victim all --interval 1 --print
pt-kill --no-version-check --host 127.0.0.1 --port 3306 --user root --password 111111 --socket /opt/mysql3306/data/mysql3306.sock --match-command="Sleep|Query" --victim all --interval 1 --print

image

当command匹配为Sleep时,只打印了command为Sleep的匹配查询

当command匹配为Sleep和Query时,打印了command为Sleep和Query的匹配查询

多个条件匹配时,用|分割

State匹配查询

1.通过pt-kill连接state匹配并打印出来,分别打印state只包括starting和包括starting及Creating sort index和包括Creating sort index及Sending data的示例

1
2
3
pt-kill --no-version-check --host 127.0.0.1 --port 3306 --user root --password 111111 --socket /opt/mysql3306/data/mysql3306.sock --match-state="starting" --victim all --interval 1 --print
pt-kill --no-version-check --host 127.0.0.1 --port 3306 --user root --password 111111 --socket /opt/mysql3306/data/mysql3306.sock --match-state="starting|Creating sort index" --victim all --interval 1 --print
pt-kill --no-version-check --host 127.0.0.1 --port 3306 --user root --password 111111 --socket /opt/mysql3306/data/mysql3306.sock --match-state="Creating sort index|Sending data" --victim all --interval 1 --print

image

当state匹配为starting时,只打印了state为starting的匹配查询

当state匹配为starting和Creating sort index时,打印了state为starting和Creating sort index的匹配查询

当state匹配为Creating sort index和Sending data时,打印了state为Creating sort index和Sending data的匹配查询

Info匹配查询

1.通过pt-kill连接info匹配并打印出来,分别打印info只包括COMMIT和只包括SELECT和包括COMMIT及SELECT的示例

1
2
3
pt-kill --no-version-check --host 127.0.0.1 --port 3306 --user root --password 111111 --socket /opt/mysql3306/data/mysql3306.sock --match-info="COMMIT" --victim all --interval 1 --print
pt-kill --no-version-check --host 127.0.0.1 --port 3306 --user root --password 111111 --socket /opt/mysql3306/data/mysql3306.sock --match-info="SELECT|COMMIT" --victim all --interval 1 --print
pt-kill --no-version-check --host 127.0.0.1 --port 3306 --user root --password 111111 --socket /opt/mysql3306/data/mysql3306.sock --match-info="SELECT" --victim all --interval 1 --print

image

当info匹配为COMMIT时,只打印了info为starting的匹配查询

当info匹配为SELECT时,只打印了info为SELECT的匹配查询

当info匹配为COMMIT和SELECT时,打印了info为SELECT和COMMIT的匹配查询

组合匹配查询

1.通过pt-kill连接匹配规则如下

  • 匹配host为127.0.0.1
  • 匹配user为root
  • 匹配db为sysbench
  • 匹配command为Query
  • 匹配state为Opening tables
  • 匹配info为SELECT
    1
    pt-kill --no-version-check --host 127.0.0.1 --port 3306 --user root --password 111111 --socket /opt/mysql3306/data/mysql3306.sock --match-host="127.0.0.1" --match-user="root" --match-db="sysbench" --match-command="Query" --match-state="Opening tables" --match-info="SELECT" --victim all --interval 1 --print

image

只有匹配上面所有的条件,才会打印出来匹配的SQL详情

Ignore忽略匹配

1.通过pt-kill连接匹配规则如下

  • 匹配host为127.0.0.1
  • 忽略匹配info为SELECT
    1
    pt-kill --no-version-check --host 127.0.0.1 --port 3306 --user root --password 111111 --socket /opt/mysql3306/data/mysql3306.sock --match-host="127.0.0.1" --ignore-info="SELECT" --victim all --interval 1 --print

image

当指定ignore时,ignore具有最高的优先级,再去匹配其他规则

DSN匹配查询

1.通过pt-kill连接匹配,结合ignore并打印出来,同时记录到DNS表中,规则如下

  • 匹配host为127.0.0.1
  • 匹配user为root
  • 匹配db为sysbench
  • 匹配command为Query
  • 匹配state为Opening tables
  • 匹配info为SELECT
  • 忽略匹配info为update
    1
    pt-kill --no-version-check --log-dsn D=test,t=kill_log --create-log-table --host 127.0.0.1 --port 3306 --user root --password 111111 --socket /opt/mysql3306/data/mysql3306.sock --match-host="127.0.0.1" --match-user="root" --match-db="sysbench" --match-command="Query" --match-state="Sending data" --match-info="SELECT" --ignore-info=”update” --victim all --interval 1 --kill-query

image

2.结果分析

只有匹配上面所有的条件,才会将kill的SQL详情保存在test库下的kill_log表中,DSN模式下使用print不会记录到表kill_log中

3.Kill_log表结构

image

4.kill_log表记录

image

建议

方法推荐

  • 推荐使用组合匹配加上ignore模式精确匹配,减小误操作的几率
  • 先测试再执行,推荐用print方法,也可使用–test-matching方法
  • 推荐使用DSN模式,能够记录pt-kill的历史记录,方便分析,追溯
  • 推荐使用–kill-query替代–kill,–kill-query对应用更友好
  • 推荐使用后台进程方式–daemonize结合–run-time使用

语句推荐

1.查询推荐

1
pt-kill --no-version-check --log-dsn D=test,t=kill_log --create-log-table --host 127.0.0.1 --port 3306 --user root --password 111111 --socket /opt/mysql3306/data/mysql3306.sock --match-host="127.0.0.1|localhost" --match-user="root" --match-db="sysbench|test" --match-command="Query" --match-state="Sending data|User sleep" --match-info="(?i-xsm:select)" --ignore-info="(?i-smx:^insert|^update|^delete|^load)" --victim all --interval 1 --print

  1. kill语句保存到DSN并输出到log
    1
    pt-kill --no-version-check --log-dsn D=test,t=kill_log --create-log-table --host 127.0.0.1 --port 3306 --user root --password 111111 --socket /opt/mysql3306/data/mysql3306.sock --match-host="127.0.0.1|localhost" --match-user="root" --match-db="sysbench|test" --match-command="Query" --match-state="Sending data|User sleep" --match-info="(?i-xsm:select)" --ignore-info="(?i-smx:^insert|^update|^delete|^load)" --victim all --interval 1 --kill-query --daemonize --print --log=/tmp/pt-kill.log

3.kill语句仅输出到log

1
pt-kill --no-version-check --host 127.0.0.1 --port 3306 --user root --password 111111 --socket /opt/mysql3306/data/mysql3306.sock --match-host="127.0.0.1|localhost" --match-user="root" --match-db="sysbench|test" --match-command="Query" --match-state="Sending data|User sleep" --match-info="(?i-xsm:select)" --ignore-info="(?i-smx:^insert|^update|^delete|^load)" --victim all --interval 1  --kill-query --daemonize --print --log=/tmp/pt-kill.log

生产环境

当生产出现并发队列高导致cpu高时,查看如果为查询引起的话,可直接根据时间来kill,但是可能会造成误杀,在处理问题速度为主的情况下,此种方式也可选择

打印select大于5s的查询

1
pt-kill --no-version-check --socket /opt/mysql3306/data/mysql.sock --match-info="(?i-xsm:select)" --busy-time 5s --victim all --interval 1 --print

kill大于5s的查询

1
pt-kill --no-version-check --socket /opt/mysql3306/data/mysql.sock --match-info="(?i-xsm:select)" --busy-time 5s --victim all --interval 1 --print --kill-query --daemonize --log=/tmp/pt-kill.log

-------------本文结束感谢您的阅读-------------