MySQL pt-kill & pt-deadlock-logger 死锁检测

一、 pt-kill

1. 常用参数

•   --match-command：匹配 show processlist 中的  Command 字段，常用的有 Query,Sleep,Binlog Dump,Connect,Delayed insert,Execute,Fetch,Init DB,Kill,Prepare,Processlist,Quit,Reset stmt,Table Dump

mysql> show processlist;
+----+------+-----------+------+---------+------+----------+------------------+
| Id | User | Host      | db   | Command | Time | State    | Info             |
+----+------+-----------+------+---------+------+----------+------------------+
|  5 | root | localhost | test | Query   |    0 | starting | show processlist |
+----+------+-----------+------+---------+------+----------+------------------+
1 row in set (0.00 sec)

•   --match-db：对应 show processlist 中的  db 字段
•   --match-host：对应 show processlist 中的  host 字段，支持正则表达式
•   --match-info：对应 show processlist 中的  info 字段
•   --match-state：对应 show processlist 中的  state 字段，支持正则表达式
•   --match-user：对应 show processlist 中的  user 字段，支持正则表达式
•   --busy-time：匹配运行时间大于指定时间的会话
•   --idle-time：匹配 idle/sleeping 时间大于指定时间的会话
• --password 显示输入密码
• --ask-pass 隐示输入密码
• --victims all：需要有，否则只会处理一个连接（最早连上数据库的那个）
• --interval 30：默认每30秒循环执行一次，可以根据实际情况调整，如果不加，只执行一次
• --run-time：pt-kill工具运行多长时间后自动退出，默认是永久
• 默认会过滤掉复制线程，请不必担心杀掉复制线程。

2. 常用命令

每10秒打印出sleep超过100秒的root用户连接信息（不kill）

pt-kill --idle-time 100 --victims all --interval 10 --host xxx --port 3306 --user root --ask-pass --print

每10秒杀掉来自 192.168.2.123 的所有sleep超过200s的连接

pt-kill --victims all --match-host '192.168.2.123' --match-command 'Sleep' --idle-time 200 --interval 10 --host xxx --port 3306 --user root --ask-pass --kill --print 

每10秒杀掉执行时间超过5秒、符合指定sql的连接：

pt-kill --victims all --busy-time 5 --match-info "select id,image,image2,image3,maidian,title,team_price,market_price.*" --interval 10 --host xxxx --port 3306 --user root --ask-pass --print --kill

每10秒杀掉来自xldev用户的所有执行超过10s的连接

pt-kill --no-version-check --host 10.13.2.66 --port 3306 --user root --ask-pass  --charset utf8 --match-command Query --match-user xldev --busy-time 10 --victims all --interval 10 --kill  --print

二、 pt-deadlock-logger

pt-deadlock-logger 检测数据库的死锁信息，相比show engine innodb status; 更方便观察，可以记录多条死锁信息，还可以同时监控多个MySQL中的死锁情况，不过需要提前运行。

1. 常用参数

• --ask-pass：不显示密码
• --tab：输出带上制表符，格式化显示，更加清晰
• --log：将死锁信息输出至指定日志文件
• --daemonize：放到后台运行，守护进程模式
• --interval：每隔多少秒检测一次，默认30
• --run-time：运行时间
• --iterations：检测的次数
• --dest：将死锁信息输出至指定数据库的表中（表需要手动建）

2. 使用示例

• 结果输出至log文件

pt-deadlock-logger  -uroot -h检测的IP -P3306 --ask-pass --interval=1 --tab  --daemonize --log=/tmp/pt-deadlock-logger.log 
#监控输出
tailf /tmp/pt-deadlock-logger.log 

输出信息类似如下，有点丑

server ts thread txn_id txn_time user hostname ip db tbl idx lock_type lock_mode wait_hold victim query 
192.168.112.129 2018-02-12T15:19:17 20 0 20 root 192.168.112.1 player player PRIMARY RECORD X w 1 UPDATE `player`.`player` SET `role` = 'goalkeeper' WHERE `id` = 2192.168.112.129 2018-02-12T15:19:17 21 0 7 root 192.168.112.1 player player PRIMARY RECORD X w 0 UPDATE `player`.`player` SET `role` = 'leftdefender' WHERE `id` = 1

• 结果输出至指定表中

先建表

CREATE TABLE `player`.`deadlocks` (
  server char(20) NOT NULL,
  ts timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  thread int unsigned NOT NULL,
  txn_id bigint unsigned NOT NULL,
  txn_time smallint unsigned NOT NULL,
  user char(16) NOT NULL,
  hostname char(20) NOT NULL,
  ip char(15) NOT NULL, -- alternatively, ip int unsigned NOT NULL
  db char(64) NOT NULL,
  tbl char(64) NOT NULL,
  idx char(64) NOT NULL,
  lock_type char(16) NOT NULL,
  lock_mode char(1) NOT NULL,
  wait_hold char(1) NOT NULL,
  victim tinyint unsigned NOT NULL,
  query text NOT NULL,
  PRIMARY KEY  (server,ts,thread)
) ENGINE=InnoDB;

运行pt-deadlock-logger，--dest h=192.168.112.128,P=3306,u=root,p=123456,D=player,t=deadlocks 指定将死锁信息存在192.168.112.128的player库的deadlocks表中，可以与检测的ip不同。

pt-deadlock-logger  -uroot -h检测的IP -P3306 --ask-pass --interval=1 --tab  --daemonize --dest h=192.168.112.128,P=3306,u=root,p=123456,D=player,t=deadlocks

结果更方便看

3. 简单原理

pt-deadlock-logger通过查询和解析 SHOW ENGINE INNODB STATUS 来打印MySQL死锁信息。当新的死锁出现时，就会被打印到STDOUT或者保存到表/日志。

     新的死锁才会被打印。通过死锁的元数据“ server ”、“ ts ”和“ thread ”值（即便这些字段没有被“ --columns ”选项指定），每个死锁会生成一个fingerprint。如果死锁的fingerprint值异于上一个死锁的fingerprint，这个死锁就会被打印。

参考：
http://keithlan.github.io/2018/11/23/pt_kill_doc/
https://www.percona.com/doc/percona-toolkit/LATEST/pt-kill.html#cmdoption-pt-kill-kill

https://dev.mysql.com/doc/refman/5.7/en/thread-commands.html
https://dev.mysql.com/doc/refman/5.7/en/general-thread-states.html

Percona-Toolkit系列之pt-deadlock-logger数据库死锁记录利器 | | For DBA

Percona Toolkit使用之pt-deadlock-logger_蛙鳜鸡鹳狸猿的博客-CSDN博客