介绍
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
82018-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不kill1
pt-kill --busy-time 60 --print
3.每10s检查sleep进程并kill1
pt-kill --match-command Sleep --kill --victims all --interval 10
4.打印所有的登录进程1
pt-kill --match-state login --print --victims all
5.查看当前进程列表中的查询匹配1
2mysql -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 | 只杀掉连接执行的语句，线程会被终止 |
| 打印匹配查询的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 //mysql3306//mysql3306.sock -e "SHOW PROCESSLIST" > proclist.txt
2.查看proclist.txt文件内容
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
可以看出测试匹配出了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
仅匹配了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
仅匹配了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
仅匹配了user为root,并且未指定其他匹配,从输出上看command为Query,Sleep都打印了出来
Command匹配查询
1.通过pt-kill连接command匹配并打印出来,分别打印c
mmand只包括Sleep和包括Sleep及Query的示例1
2pt-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
当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
3pt-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
当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
3pt-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
当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
只有匹配上面所有的条件,才会打印出来匹配的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
当指定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
2.结果分析
只有匹配上面所有的条件,才会将kill的SQL详情保存在test库下的kill_log表中,DSN模式下使用print不会记录到表kill_log中
3.Kill_log表结构
4.kill_log表记录
建议
方法推荐
- 推荐使用组合匹配加上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
- 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语句仅输出到log1
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