Percona-Toolkit系列(5) --- pt-deadlock-logger
原创
©著作权归作者所有:来自51CTO博客作者Expect_lei的原创作品,请联系作者获取转载授权,否则将追究法律责任
pt-deadlock-logger
说明
用途:记录MySQL死锁信息。
语法:
pt-deadlock-logger [OPTIONS]
注:pt-deadlock-logger在给定的DSN上记录关于MySQL死锁的信息。信息被打印到STDOUT,也可以通过指定—dest保存到表中。除非指定了--run-time
(时间)或--iterations
(次数),否则该工具将永远运行。
具体例子
将死锁信息打印到屏幕上,并不插入到表中。
[root@BigData ~]# pt-deadlock-logger h=192.168.20.5,u=root,p=Sanshi_408,P=3306
server ts thread txn_id txn_time user hostname ip db tbl idx lock_type lock_mode wait_hold victim query
192.168.20.5 2019-07-26T11:58:01 352114 0 492 root localhost lei test age RECORD X w 1 delete from test where age=1
192.168.20.5 2019-07-26T11:58:01 358455 0 483 root localhost lei test age RECORD X w 0 delete from test where age=1
将死锁信息插入到表中deadlocks
[root@BigData ~]# pt-deadlock-logger --ask-pass --create-dest-table D=test,t=deadlocks h=192.168.20.5,u=root,p=Sanshi_408,P=3306
server ts thread txn_id txn_time user hostname ip db tbl idx lock_type lock_mode wait_hold victim query
192.168.20.5 2019-07-26T11:58:01 352114 0 492 root localhost lei test age RECORD X w 1 delete from test where age=1
192.168.20.5 2019-07-26T11:58:01 358455 0 483 root localhost lei test age RECORD X w 0 delete from test where age=1
可以通过查看表deadlocks
来了解死锁信息。
deadlocks
表结构
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,
PRIMARY KEY (server,ts,thread)
) ENGINE=InnoDB