使用pt-kill终止长时间运行的MySQL查询

percona pt-kill是一个优秀的kill MySQL连接的工具，是percona Toolkit的一部分，主要用于监控和管理 MySQL 数据库中的长时间运行的查询。它可以帮助数据库管理员自动终止那些可能影响性能的慢查询。

基本原理

pt-kill从show processlist 中获取满足条件的连接或者从包含show processlist的文件中读取满足条件的连接并打印或者杀掉或者执行其他操作。

安装

  1. 安装依赖
    yum install perl-DBI yum install perl-DBD-MySQL yum install perl-Time-HiRes yum install perl-IO-Socket-SSL perl-Digest-MD5
  2. 安装
    wget https://downloads.percona.com/downloads/percona-toolkit/3.7.0/binary/redhat/9/x86_64/percona-toolkit-3.7.0-1.el9.x86_64.rpm
rpm -ivh percona-toolkit-3.7.0-1.el9.x86_64.rpm

参数

参数解释
–host指定要连接的mysql主机名或者ip地址
–port指定要连接的mysql 端口
–user指定连接mysql的用户名
–password指定连接mysql的密码
–match-user匹配特定的用户名
–match-host匹配特定的主机名或者ip地址
–match-db匹配指定的数据库名
–match-command匹配指定的命令，比如Sleep、Query
–busy-time/idle-time匹配执行时间超过或者空闲时间超过指定值的连接，单位是秒或者分钟
–match-state匹配特定的状态，例如Sleep、Running等等
–match-info匹配连接的其他信息，例如查询语句
–ignore-user忽略特定的用户名
–ignore-host忽略特定的主机名或者ip地址
–ignore-db忽略特定的数据库名
–ignore-command忽略指定的名
–ignore-state忽略指定的状态
–ignore-info忽略连接的其他信息
–kill杀掉匹配到的连接并且推出会话
–kill-query只杀掉执行的语句，不推出会话
–print只打印匹配到的会话，不kill
–victims指定要kill的连接类型，可以是oldest（只杀掉最古老的查询）或者all（杀掉所有匹配到的查询）
–daemonize以守护进程的形式运行
–interval间隔多久运行一次，默认30s
–run-time运行多久后退出
–config指定配置文件
–group-by按照指定的条件对查询结果进行分组

使用

  1. kiil超过60s的查询

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

  2. 打印查询超过60s的查询

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

  3. 杀掉查询超过5秒的

    pt-kill --host xxxxx --port 3306 --user root --password xxxxxxx --match-command Query --busy-time 5 --kill --victims all

  4. 每10s 去检查sleep 状态的query ， 并kill掉

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

  5. 打印所有login 状态的 query

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

  6. 杀掉用户user1和user2的查询并且打印日志

    pt-kill --busy-time 15   --match-user="user1 | user2" --victim all --interval 1 --kill --daemonize --pid=/tmp/ptkill.pid --print --log=/home/pt-kill.log

  7. 按query来源host

    pt-kill --busy-time 15   --match-host="192.168.1.10 | 192.168.1.11" --victim all --interval 1 --kill --daemonize --pid=/tmp/ptkill.pid --print --log=/home/pt-kill.log

  8. 按command kill

    pt-kill --busy-time 15   --match-command="query | Execute" --victim all --interval 1 --kill --daemonize --pid=/tmp/ptkill.pid --print --log=/home/pt-kill.log
pt-kill --busy-time 15   --ignore-command="sleep | binlogdump" --victim all --interval 1 --kill --daemonize --pid=/tmp/ptkill.pid --print --log=/home/pt-kill.log

  9. 严格区分大小写，command有：Query Sleep/Binlog Dump/Connect/Delayed insert/Execute/Fetch/InitDB/Kill/Prepare/Processlist/Quit/Reset stmt/Table Dump

    pt-kill --match-command Query --busy-time 5 --host --port --interval --print --kill --victims all

  10. 按state kill

    pt-kill --busy-time 15 --match-state="Locked | Sending data" --victim all --interval 1 --kill --daemonize --pid=/tmp/ptkill.pid --print --log=/home/pt-kill.log

  11. 按info关键字kill

    pt-kill --busy-time 15 --match-info="SELECT | DELETE" --victim all --interval 1 --kill --daemonize --pid=/tmp/ptkill.pid --print --log=/home/pt-kill.log

  12. 按访问的dbname kill

    pt-kill --busy-time 15 --match-db="db1 | db2"  --victim all --interval 1 --kill --daemonize --pid=/tmp/ptkill.pid --print --log=/home/pt-kill.log

  13. 杀掉空闲连接

    pt-kill --match-command Sleep --idle-time 100 --host hostname --port 5102 --interval 0.5 --print --kill --victims all

  14. 杀掉运行时间超过5s的连接

    pt-kill --match-command Query --busy-time 5 --host --port --interval --print --kill --victims all

  15. 杀掉匹配某个规则的正在运行的SQL

    pt-kill --match-command Query --busy-time 5 --host --port --interval --print --kill --victims all --match-info="select"

  16. 杀掉正在filesort的SQL

    pt-kill --match-command Query --match-state "Sorting result" --busy-time 5 --host –port --interval --print --kill --victims all

  17. 杀掉正在Copying to tmp table的SQL

    pt-kill --match-command Query --match-state "Copying to tmp table" --busy-time 5 --host --port --interval --print --kill --victims all

参考