pt-kill
######################
pt-kill作用：杀会话问题
一：服务器主动kill
针对select查询，仅需要设置相关参数即可达成目标：
-- mysql 5.7 设置select时间不得超过6秒
set global max_execution_time = 6000；
-- mysql 5.6 设置select时间不得超过6秒
set global max_statement_time = 6000；
批量查杀会话线程：
root@c4-using-glc-db05.bj((none)) > show full processlist; +------+-----------+---------------------+----------+-------------+-------+-----------------------------------------------------------------------+------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +------+-----------+---------------------+----------+-------------+-------+-----------------------------------------------------------------------+------------------------------------------------+ | 688 | mysqlsync | 192.136.42.3:25019 | NULL | Binlog Dump | 271924 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL | | 4230 | glc_x | 192.142.85.32:22870 | NULL | Sleep | 2 | | NULL | | 4236 | glc_x | 192.142.86.31:37761 | NULL | Sleep | 4 | | NULL | | 4237 | glc_x | 192.142.86.31:37763 | NULL | Sleep | 30 | | NULL | | 4238 | glc_x | 192.142.86.31:37765 | NULL | Sleep | 30 | | NULL | | 4239 | glc_x | 192.142.86.31:37767 | NULL | Sleep | 90 | | NULL | | 4242 | glc_x | 192.114.4.44:47747 | NULL | Sleep | 3 | | NULL | | 4243 | glc_x | 192.114.4.44:47751 | NULL | Sleep | 30 | | NULL | | 4244 | glc_x | 192.114.4.44:47757 | NULL | Sleep | 30 | | NULL | | 4245 | glc_x | 192.114.4.44:47759 | NULL | Sleep | 90 | | NULL | | 4349 | orch | 192.136.142.42:57492 | NULL | Sleep | 2 | | NULL | | 4350 | orch | 192.136.142.42:57494 | NULL | Sleep | 2 | | NULL | | 4353 | orch | 192.132.67.31:18692 | NULL | Sleep | 4 | | NULL | | 4354 | orch | 192.132.67.31:18693 | NULL | Sleep | 4 | | NULL | | 4356 | orch | 192.136.142.42:57982 | NULL | Sleep | 1 | | NULL | | 4584 | mysqlsync | 192.142.66.14:18372 | NULL | Binlog Dump | 13885 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL | | 7629 | orch | 192.132.67.31:17367 | NULL | Sleep | 4 | | NULL | | 7630 | orch | 192.136.142.42:55390 | NULL | Sleep | 2 | | NULL | | 8149 | root | 192.10.10.10:39812 | NULL | Query | 0 | NULL | show full processlist | | 8535 | glc_x | 192.142.85.32:49416 | tv_ashes | Killed | 17 | Sorting result | select episode from station_sound order by ext | | 8633 | root | 192.10.10.10:42006 | NULL | Sleep | 1 | | NULL | +------+-----------+---------------------+----------+-------------+-------+-----------------------------------------------------------------------+------------------------------------------------+
pt-kill --user=root --password=123456 --host=c4-using-glc-db05.bj --port=3306 --match-user="igoodful" --match-host="10.10.10.10|10.10.10.11" --match-db="apple" --match-command="Sleep|Query" --match-state="Sorting result" --match-info="select|SELECT" --busy-time 15 --victims all --interval 10 --kill --print
pt-kill --host=HHH --port=3306 --user=UUU --password=ppp --match-db='mifm_backend' --match-info "select|SELECT" --busy-time 30 --victims all --interval 10 --daemonize --kill --log=./t_kill.log
数据库连接四大要素
--host=主机名称 --port=端口号 --user=用户名称 --password=用户密码
常见参数详解
/usr/local/bin/pt-kill --host=HHH --port=PPP --user=UUU --password=ppp --match-info=select|SELECT --victims=all --interval 60 --busy-time 160 --kill --print --daemon --print 打印 --kill 杀掉连接线程切退出 --kill-query 只杀掉连接执行的语句，但是线程不会被终止 --daemonize --log=./pt-kill.log --victims all 匹配当前的所有连接 --interval 10 命令循环执行的间隔周期是10秒，即每隔10秒执行一次 --ignore-db='sys,mysql,information_schema,performance_schema' --busy-time 30 超过30秒的sql --match-host "10.10.10.10 | 10.10.10.11" 匹配来源会话线程的ip地址 #information_schema.processlist表的host字段 --match-user "cas_x" 匹配会话线程的用户 #information_schema.processlist表的user字段 --match-db='cas' #information_schema.processlist表的db字段 --match-command='Query | Execute' 匹配会话连接线程的命令 #information_schema.processlist表的command字段 --ignore-command="sleep | binlogdump" 忽略掉命令的线程，与--match-command相反 --busy-time 匹配运行连接时间超过该值的会话连接线程 #information_schema.processlist表的time字段 --idle-time 5 匹配空闲连接时间超过该值的会话连接线程 #information_schema.processlist表的time字段 --match-state="Sorting result | Locked | Sending data" 匹配会话线程的状态 #information_schema.processlist表的state字段 --match-info "SELECT | select" 匹配会话线程的状态 #information_schema.processlist表的info字段 --ignore-info="DELETE | UPDATE"
###################
igoodful@qq.com
