使用pt-deadlock-logger分析mysql死锁问题

pt-deadlock-logger

pt-deadlock-loggerpercona是Percona Toolkit中的一个工具，主要用于死锁检测。

本质就是一个连接到数据库服务器的脚本，用于探测死锁，一旦有死锁发现，就会从数据库中采集死锁相关信息，并输出到指定的地方，比如：标准输出、文件、表。

 

官方文档：https://docs.percona.com/percona-toolkit/pt-deadlock-logger.html

主要参数

--host：-h，IP地址
--port：-P，端口
--socket：-S，套接字文件
--user：-u，用户名
--password：-p，密码
--charset：-A，字符集
--database：-D，数据库
--ask-pass：提示手动输入密码
--daemonize：后台运行
--dest：指定存储死锁信息的表
--create-dest-table：自动创建存储死锁信息的表
--columns：死锁信息存储表的列信息
--log：当daemonize模式运行时，输出死锁信息到日志文件
--run-time：运行时间，默认永久
--interval：运行间隔，默认30s
--iterations：运行次数，默认无限
--config：指定配置文件

安装pt-deadlock-logger

安装Percona Toolkit

1.配置Percona repositories

基于rpm的发行版，例如Red Hat Enterprise Linux或CentOS，可以使用yum包管理器安装percona-release。

sudo yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm

2.使用相应的包管理器安装Percona Toolkit

For RHEL or CentOS:

sudo yum install percona-toolkit

使用pt-deadlock-logger监控死锁

模拟死锁

模拟数据

create table tb1(id int, a varchar(20));
insert into tb1 values(1,'1'),(2,'2');

执行操作

使用pt-deadlock-logger打印死锁信息

检测结果到标准输出

pt-deadlock-logger h=localhost,P=3306,u=user_name,p=password

检测结果输出到日志

pt-deadlock-logger h=localhost,P=3306,u=user_name,p=password --log=/tmp/pt_deadlock.log --daemonize

检测结果输出到表

可以使用--create-dest-table，自动创建存储死锁信息的表。也可以提前把表建了。建表语句：

CREATE TABLE deadlocks
(
    server    char(20)          NOT NULL COMMENT '发生死锁的 MySQL 实例，IP:PORT 标识',
    ts        timestamp         NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '发生死锁的时间',
    thread    int UNSIGNED      NOT NULL COMMENT '该事务所属的线程ID，及应用端连接的会话ID 信息',
    txn_id    bigint UNSIGNED   NOT NULL COMMENT '事务ID',
    txn_time  smallint UNSIGNED NOT NULL COMMENT '事务执行时间',
    user      char(16)          NOT NULL COMMENT '应用连接的用户',
    hostname  char(20)          NOT NULL COMMENT '应用端 hostname 名',
    ip        char(15)          NOT NULL COMMENT '应用端 ip 地址', -- alternatively, ip int unsigned NOT NULL
    db        char(64)          NOT NULL COMMENT '事务语句所在的 database',
    tbl       char(64)          NOT NULL COMMENT '事务关联的表',
    idx       char(64)          NOT NULL COMMENT '使用的索引信息',
    lock_type char(16)          NOT NULL COMMENT '当前事务语句持有锁的类型',
    lock_mode char(1)           NOT NULL COMMENT '引起死锁的锁模式（S，X 等）',
    wait_hold char(1)           NOT NULL COMMENT '该事务是否在等待锁（w） 还是在持有锁（h）',
    victim    tinyint UNSIGNED  NOT NULL COMMENT '1 表示该事务被回滚',
    query     text              NOT NULL COMMENT '事务的 sql 语句（注：innodb status 只显示该事务中最后更新的一条 sql，如果一个事务有多条更新语句，之前的 sql 不会显示出来）',
    PRIMARY KEY (server, ts, thread)
) ENGINE = InnoDB;

使用--dest指定指定存储死锁信息的表，命令：

pt-deadlock-logger h=localhost,P=3306,u=user_name,p=password --dest h=xxx.xxx.xxx.xxx,P=3306,D=db_name,t=deadlocks,u=user_name,p=password

使用SELECT * FROM deadlocks
 命令查询死锁信息。

mysql> select * from deadlocks;

+-----------+---------------------+--------+--------+----------+---------+----------+----------------+------------+---------+---------+-----------+-----------+-----------+--------+----------------------------------------------------------------------------------------+
| server    | ts                  | thread | txn_id | txn_time | user    | hostname | ip             | db         | tbl     | idx     | lock_type | lock_mode | wait_hold | victim | query                                                                                  |
+-----------+---------------------+--------+--------+----------+---------+----------+----------------+------------+---------+---------+-----------+-----------+-----------+--------+----------------------------------------------------------------------------------------+
| 127.0.0.1 | 2023-02-08T17:44:52 |    13025720 |      0 |      142 | beanbag |          | 115.236.000.00 | dmeo       | tb1 | GEN_CLUST_INDEX | RECORD    | X         | w         |      0 | update tb1 set a = '11' where id = 2 |
| 127.0.0.1 | 2023-02-08T17:44:52 |    13025795 |      0 |      140 | beanbag |          | 115.236.000.00 | dmeo       | tb1 | GEN_CLUST_INDEX | RECORD    | X         | w         |      1 | update tb1 set a = '11' where id = 1 |
+-----------+---------------------+--------+--------+----------+---------+----------+----------------+------------+---------+---------+-----------+-----------+-----------+--------+----------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

使用配置文件

当前目录下创建配置文件pt-deadlock.cnf，写入如下内容

#login to MySQL server#
host=#host
port=3306
#
user=xx
password=yy
charset=utf8
#
#log to file
tab
log=/usr/local/log/pt_deadlock.log
#output to table
#dest=h=xxx.xxx.xxx.xxx,P=3306,D=db_name,t=deadlocks,u=user_name,p=password
#后台运行
daemonize
#运行间隔为5s
interval=5

执行命令：

pt-deadlock-logger --config ./pt-deadlock.cnf

pt-deadlock-logger的输出参数

server:数据库服务器地址，即死锁产生的数据库主机;
ts:检测到死锁的时间戳;
thread:产生死锁的线程id，这个id和show processlist里面的线程id是一致的;
txn_id:innodb的事务ID;
txd_time:死锁检查到前，事务执行时间;
user:执行transcation的用户名;
hostname:客户端主机名;
ip:客户端ip;
db:发生死锁的DB名;
tbl:死锁发生的表名;
idx:产生死锁的索引名;
lock_type:锁的类型(记录锁，gap锁，next-key锁);
lock_mode:锁模式(S,X);
wait_hold:是否等着锁释放;
victim:该会话是否做了牺牲，终止了执行;
query:造成死锁的SQL语句;

server ts thread txn_id txn_time user hostname ip db tbl idx lock_type lock_mode wait_hold victim query
127.0.0.1 2023-02-08T17:44:52 13025720 0 127 demo 127.0.0.1 zhenxi_test tb1 GEN_CLUST_INDEX RECORD X w 0 update tb1 set a = '11' where id = 2
127.0.0.1 2023-02-08T17:44:52 13025795 0 8 demo 127.0.0.1 zhenxi_test tb1 GEN_CLUST_INDEX RECORD X w 1 update tb1 set a = '11' where id = 1

可以看到有了这些信息，可以十分方便地辅助我们分析死锁信息，比我们直接执行show engine innodb status\G；
命令拿到的死锁日志要清晰很多，并且还可以帮助我们把历史的死锁日志保存下来，方便排查问题。

总结

pt-deadlock-logger是Percona Toolkit中的一个工具，主要用于死锁检测。可以帮助我们从数据库中采集死锁相关信息，并输出到指定的地方，比如：标准输出、文件、表。输出信息更清晰简洁，可以帮助我们更便捷地排查死锁问题。

参考文档：

https://docs.percona.com/percona-toolkit/pt-deadlock-logger.html
https://juejin.cn/post/7106896179480231949
https://blog.csdn.net/m0_37389157/article/details/123600648

点个“赞 or 在看” 你最好看！

👇👇👇 咔片谢谢各位老板啦！！！