利用pt-deadlock-logger监控死锁

Percona提供的percona-toolkit提供很多实用功能,这里着重介绍如何监控死锁.

pt-deadlock-logger基本用法

Usage: pt-deadlock-logger [OPTIONS] DSN

解释:指定DSN死锁信息,它可以标准输出到屏幕也可以记入到表中或者日志文件,OPTIONS就是要加的参数了.

在这里做个模拟产生死锁的试验,例子如下:

session1:

mysql> create table lock1 (id int,name char(20),address varchar(50));
Query OK, 0 rows affected (0.15 sec)

mysql> create table lock2 (id int,name char(20),address varchar(50));
Query OK, 0 rows affected (0.15 sec)

mysql> insert into lock1 values (1,'aa','beijing');
Query OK, 1 row affected (0.00 sec)

mysql> insert into lock1 values (2,'bb','shanghai');
Query OK, 1 row affected (0.02 sec)

mysql> insert into lock1 values (3,'cc','shenzhen');
Query OK, 1 row affected (0.01 sec)

mysql> insert into lock2 values (1,'dd','beijing');                   
Query OK, 1 row affected (0.01 sec)

mysql> insert into lock2 values (2,'ee','tianjin'); 
Query OK, 1 row affected (0.01 sec)

mysql> insert into lock2 values (3,'ff','henan');   
Query OK, 1 row affected (0.01 sec)

mysql> select * from lock1;
+------+------+----------+
| id   | name | address  |
+------+------+----------+
|    1 | aa   | beijing  |
|    2 | bb   | shanghai |
|    3 | cc   | shenzhen |
+------+------+----------+
3 rows in set (0.01 sec)

mysql> select * from lock2;
+------+------+---------+
| id   | name | address |
+------+------+---------+
|    1 | dd   | beijing |
|    2 | ee   | tianjin |
|    3 | ff   | henan   |
+------+------+---------+
3 rows in set (0.00 sec)

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from lock1 where id=1 for update;   
+------+------+---------+
| id   | name | address |
+------+------+---------+
|    1 | aa   | beijing |
+------+------+---------+
1 row in set (0.00 sec)

session2
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> set autocommit=0;                            
Query OK, 0 rows affected (0.00 sec)

mysql>  select * from lock2 where id=1 for update;  
+------+------+---------+
| id   | name | address |
+------+------+---------+
|    1 | dd   | beijing |
+------+------+---------+
1 row in set (0.00 sec)

session1
mysql> select * from lock2 where id=1 for update; ---锁等待

session2
mysql> select * from lock1 where id=1 for update;   
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

OK,已经产生死锁了.现在用pt-deadlock-logger分析一下
[root@localhost ~]# pt-deadlock-logger --run-time=10 --interval=3 --create-dest-table --dest D=test,t=deadlocks u=root,p=xxxxx
server ts thread txn_id txn_time user hostname ip db tbl idx lock_type lock_mode wait_hold victim query
localhost.localdomain 2014-01-23T16:04:02 22 0 100 root localhost  test lock2 GEN_CLUST_INDEX RECORD X w 0 select * from lock2 where id=1 for update
localhost.localdomain 2014-01-23T16:04:02 23 0 54 root localhost  test lock1 GEN_CLUST_INDEX RECORD X w 1 select * from lock1 where id=1 for update

参数解释如下:
--create-dest-table : 创建指定信息的表.
--dest              : 创建存储死锁信息的表.
--database          : -D，指定数据库.
--table             : -t，指定表名.
--log               : 指定死锁日志信息写入到文件.
--run-time          : 运行次数，默认永久.
--interval          : 运行间隔时间，默认30s.
--u,p,              : 链接数据库的信息.

继续看deadlock表里是否存储信息
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| deadlocks      |
| lock1          |
| lock2          |
+----------------+
3 rows in set (0.00 sec)

mysql> select * from deadlocks\G
*************************** 1. row ***************************
   server: localhost.localdomai
       ts: 2014-01-23 16:04:02
   thread: 22
   txn_id: 0
 txn_time: 100
     user: root
 hostname: localhost
       ip: 
       db: test
      tbl: lock2
      idx: GEN_CLUST_INDEX
lock_type: RECORD
lock_mode: X
wait_hold: w
   victim: 0
    query: select * from lock2 where id=1 for update
*************************** 2. row ***************************
   server: localhost.localdomai
       ts: 2014-01-23 16:04:02
   thread: 23
   txn_id: 0
 txn_time: 54
     user: root
 hostname: localhost
       ip: 
       db: test
      tbl: lock1
      idx: GEN_CLUST_INDEX
lock_type: RECORD
lock_mode: X
wait_hold: w
   victim: 1
    query: select * from lock1 where id=1 for update
2 rows in set (0.00 sec)

mysql> 

测试表明死锁信息已经写入到了表中,记录的是2条导致死锁的SQL还有包括库、用户等等很详细的信息.对于我们可以利用pt-deadlock-logger,查看到底哪些SQL引起死锁方便定位.