有效收集 MySQL 的死锁信息

简单介绍

收集 MySQL 的死锁信息有什么好处? 可见的包含以下好处:

1. 死锁发生的频率;
2. 哪些库的哪些表发生失败;
3. 事务执行了多久， 哪个事务被回退;
4. 方便排错, 可以和程序的日志对应;

MySQL 提供了两种方式可以用来查看死锁信息, 这两种方式受 innodb_print_all_deadlocks 参数的影响, 如果开启死锁信息会记录到 MySQL 的 error 日志中, 如果关闭可以使用 SHOW ENGINE INNODB STATUS 语句来查看死锁信息, 第一种方式适合全量收集, 第二种方式适合增量收集, 其中最新的死锁信息会覆盖掉以前的信息. 实际上这两种方式都不利于死锁信息的统一搜集, 都需要管理员做额外的汇总操作.

我们提供了 mysql-deadlock-check 工具, 以第二种方式统一收集死锁信息进行汇总, 更多信息参考 pt-deadlock-logger .

工具说明

mysql-deadlock-check 主要用来收集线上 MySQL 实例的死锁信息, 实例存在死锁信息则对其收集, 解析并入库以便于以后的统计分析. 如下所示:

  +-----+
  | DB1 |  ----------+
  +-----+            |
  +-----+            |    +----------------+             +-----------------+
  | DB2 |  ----------+--> | deadlock-check | ----------> | DB for analysis |
  +-----+            |    +----------------+             +-----------------+
    ...              |
  +-----+            |
  | DBn |  ----------+
  +-----+

线上可以在管理机中任务计划运行 mysql-deadlock-check 工具, 收集线上所有 DB 的死锁信息, 并将结果统一入库.

使用说明

依赖

perl-DBI
perl-DBD-MySQL
perl-Time-HiRes
perl-TermReadKey

RedHat/Centos 系统可使用 yum perl-DBI perl-DBD-MySQL perl-Data-Dumper perl-Time-HiRes perl-TermReadKey 安装依赖.

配置信息

etc/host.list 配置文件以 host port 形式包含所有实例的信息:

# host port
10.0.21.5 3301
10.0.21.6 3302
....

etc/pt.conf 配置文件包含 pt-deadlock-logger 的参数信息, 主要包含以下参数:

tag=laohu
interval=60
iterations=1
set-vars=wait_timeout=10000
dest=h=10.0.21.10,P=3306,D=deadlock_check,t=deadlocks,u=user_deadlock,p=xxxxxxxxx

参数简略说明:

tag:         机房标识信息, 标识该实例所属的位置, 机房或者项目名;
user:        用于收集线上 MySQL 死锁信息的用户名, 所有实例使用同一个普通用户连接;
password:    用于收集线上 MySQL 死锁信息的普通用户的密码;
interval:    未指定 interations 选项的时候, 每隔指定时间 工具 就检测一次, 默认以循环方式运行;
interations: 指定循环检测的次数, 为 1 表示执行一次退出程序. 线上机房的实例如果很多, 需要指定该参数以避免启动过多的进程;
set-vars:    指定工具连接实例后设置的会话参数;
dest:        工具收集到的死锁信息会进行解析入库, dest 选项指定分析库的连接信息, 这也需要我们在此实例中创建参数中的库表和用户;

权限说明

以上述 dest 参数为例, 死锁信息需要入库分析, 因此需要在 dest 指定的实例中创建对应的库, 表和用户:

create database deadlock_check;
use deadlock_check;
grant select,insert,update,delete on deadlock_check.* to user_deadlock@`xxx.xxx.xxx.xxx.%`;

CREATE TABLE 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,
  tag varchar(50) NOT NULL DEFAULT '',
  finger varchar(100) NOT NULL DEFAULT '',
  origmsg text NOT NULL,
  PRIMARY KEY  (server,ts,thread),
  KEY `idx_ts` (`ts`),
  KEY `idx_finger` (`finger`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

线上的实例则需要创建监控用户, 用户至少需要以下权限, 如果使用其他存在监控用户, 需要检查是否拥有 process 权限:

grant process on *.* to user_deadlcok@`xx.xxx.xxx.%`

如何启动

start_mysql_deadlock.sh 以 5 个进程并发启动检测:

cd mysql-deadlock-check
bash start_mysql_deadlock.sh

如何分析

可增量读取分析库中的表数据按需报警, 其它分析可参考使用 Grafana 展示. 下面为死锁信息的具体解释:

示例如下, 每个死锁对应两条表记录:

server : 10.0.21.5:3308
ts : 2016-09-26 09:54:12
thread : 109954
txn_id : 306881983001
txn_time : 0
user : user_log
hostname :
ip : 10.0.21.17
db : user_log
tbl : login_log
idx : GEN_CLUST_INDEX
lock_type : RECORD
lock_mode : X
wait_hold : w
victim : 0
query : update login_log set amount=amount+1, update_time=now() where user_id=458122745 and city_id=430800

server : 10.0.21.5:3308
ts : 2016-09-26 09:54:12
thread : 112021
txn_id : 306881983024
txn_time : 0
user : user_log
hostname :
ip : 10.0.21.17
db : user_log
tbl : login_log
idx : idx_logid
lock_type : RECORD
lock_mode : X
wait_hold : hw
victim : 1
query : update login_log set amount=amount+1, update_time=now() where user_id=458210063 and city_id=430800

字段信息如下:

server: 发生死锁的MySQL 实例, ip:port 标识;
ts: 发生死锁的时间
thread: 该事务所属的线程id, 及应用端连接的会话id 信息;
txn_id: 事务id 号;
txn_time: 事务执行的时间;
user: 应用连接的用户;
hostname: 应用端 hostname 名;
ip: 应用端 ip 地址;
db: 事务语句所在的database;
tbl: 事务关联的表;
idx: 使用的索引信息;
lock_type:当前事务语句持有锁的类型;
lock_mode:引起死锁的锁的模式(S,X等);
wait_hold:该事务是否在等待锁(w)还是持有锁(h);
victim: 1 表示该事务被回滚;
query: 事务的  sql  语句(注: innodb status 只显示该事务中最后更新的一条 sql, 如果一个事务有多条更新语句, 之前的 sql 不会显示出来)

对线上实例有什么影响

检测脚本仅在线上的实例执行以下 sql, 如果实例存在很多运行的事务, 则会返回较多的数据:

SHOW ENGINE INNODB STATUS

更新历史

pt-deadlock-logger 同官方的版本相比, 主要做了以下更新:

1. server 字段修改为 ip:port 信息, 以标识一台主机的多个实例;
2. 增加 tag 字段标识位置,机房或项目信息;
3. 增加 finger 字段避免工具单次检查的时候重复更新死锁信息;
4. 增加 origmsg 字段记录表记录的原始死锁信息;
5. 修复入库语句中文乱码的问题;