pt-deadlock-logger : 显示指定的DSN的死锁日志信息,他能够标准输出到屏幕也可以把信息写日志文件中(--log参数)甚至可以保留到指定的表中(--dest参数),该工具默认是永久执行,除非指定 --run-time 或 --iterations 。
常用的参数(详细参数查看--help)
例3、监控死锁信息,只输出终端,总共监控12s,每ss采集一次数据
使用方法
pt-deadlock-logger [OPTIONS] DSN
常用的参数(详细参数查看--help)
--create-dest-table :创建指定的表。
--dest :创建存储死锁信息的表。
--database :-D,指定链接的数据库。
--table :-t,指定存储的表名。
--log :指定死锁日志信息写入到文件。
--run-time :运行次数,默认永久
--interval :运行间隔时间,默认30s。
例1、监控死锁信息,将死锁信息记录到表中同时输出到终端显示
--监控死锁信息
[root@slave159 test]# pt-deadlock-logger --create-dest-table --dest D=test,t=deadlocks u=huang,p=huang,P=3306,h=192.168.60.159
<pre name="code" class="html">
--创建测试相关表
mysql> create table t(id int,name varchar(30),primary key(id))engine=innodb;
Query OK, 0 rows affected (0.09 sec)
mysql> create table t1(id int,name varchar(30),primary key(id))engine=innodb;
Query OK, 0 rows affected (0.08 sec)
mysql> insert into t values(1,'111'),(2,'222'),(3,'333'),(4,'444'),(5,'555'),(6,'666'),(7,'777'),(8,'888'),(9,'999'),(10,'101010');
Query OK, 10 rows affected (0.08 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql> insert into t1 values(1,'111'),(2,'222'),(3,'333'),(4,'444'),(5,'555'),(6,'666'),(7,'777'),(8,'888'),(9,'999'),(10,'101010');
Query OK, 10 rows affected (0.08 sec)
Records: 10 Duplicates: 0 Warnings: 0
--制造死锁
session1 session2
mysql> delete from t where id=1;
Query OK, 1 row affected (0.00 sec)
mysql> delete from t1 where id=1;
Query OK, 1 row affected (0.00 sec)
mysql> delete from t1 where id=1;
Query OK, 1 row affected (6.00 sec)
mysql> delete from t where id=1;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
--检测到死锁
[root@slave159 test]# pt-deadlock-logger --create-dest-table --dest D=test,t=deadlocks u=huang,P=3306,h=192.168.60.159 -phuang
server ts thread txn_id txn_time user hostname ip db tbl idx lock_type lock_mode wait_hold victim query
192.168.60.159 2015-09-30T09:44:21 1694 0 20 root localhost test t1 PRIMARY RECORD X w 0 delete from t1 where id=1
192.168.60.159 2015-09-30T09:44:21 1695 0 11 root localhost test t PRIMARY RECORD X w 1 delete from t where id=1
--查看表中记录的死锁信息
[root@slave159 ~]# mysql -uhuang -phuang test -e "select * from deadlocks;"
Warning: Using a password on the command line interface can be insecure.
+----------------+---------------------+--------+--------+----------+------+-----------+----+------+-----+---------+-----------+-----------+-----------+--------+---------------------------+
| server | ts | thread | txn_id | txn_time | user | hostname | ip | db | tbl | idx | lock_type | lock_mode | wait_hold | victim | query |
+----------------+---------------------+--------+--------+----------+------+-----------+----+------+-----+---------+-----------+-----------+-----------+--------+---------------------------+
| 192.168.60.159 | 2015-09-30 09:44:21 | 1694 | 0 | 20 | root | localhost | | test | t1 | PRIMARY | RECORD | X | w | 0 | delete from t1 where id=1 |
| 192.168.60.159 | 2015-09-30 09:44:21 | 1695 | 0 | 11 | root | localhost | | test | t | PRIMARY | RECORD | X | w | 1 | delete from t where id=1 |
+----------------+---------------------+--------+--------+----------+------+-----------+----+------+-----+---------+-----------+-----------+-----------+--------+---------------------------+
查询日志中的相关信息:
150930 9:49:22 1689 Query SHOW /*!40100 ENGINE*/ INNODB STATUS /* pt-deadlock-logger */
例2、Ctrl+C结束掉上面的pt-deadlock-logger,再次执行
[root@slave159 test]# pt-deadlock-logger --create-dest-table --dest D=test,t=deadlocks u=huang,p=huang,P=3306,h=192.168.60.159
server ts thread txn_id txn_time user hostname ip db tbl idx lock_type lock_mode wait_hold victim query
192.168.60.159 2015-09-30T09:44:21 1694 0 20 root localhost test t1 PRIMARY RECORD X w 0 delete from t1 where id=1
192.168.60.159 2015-09-30T09:44:21 1695 0 11 root localhost test t PRIMARY RECORD X w 1 delete from t where id=1
<pre name="code" class="html"><pre name="code" class="html">查询日志中的相关信息:
150930 10:00:21 1734 Connect huang@192.168.60.159 on
1734 Query set autocommit=1
1734 Query SELECT @@SQL_MODE
1734 Query SHOW VARIABLES LIKE 'wait\_timeout'
1734 Query SET SESSION wait_timeout=10000
1734 Query SET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION'*/
1734 Query SELECT @@server_id /*!50038 , @@hostname*/
1735 Connect huang@192.168.60.159 on test
1735 Query set autocommit=0
1735 Query SELECT @@SQL_MODE
1735 Query SHOW VARIABLES LIKE 'wait\_timeout'
1735 Query SET SESSION wait_timeout=10000
1735 Query SET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION'*/
1735 Query SELECT @@server_id /*!50038 , @@hostname*/
1735 Query SET time_zone=SYSTEM /* pt-deadlock-logger */
1735 Query CREATE TABLE IF NOT EXISTS `test`.`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
1734 Query SELECT CONCAT(@@hostname, @@port)
1735 Query SELECT CONCAT(@@hostname, @@port)
1734 Query SHOW /*!40100 ENGINE*/ INNODB STATUS /* pt-deadlock-logger */
1735 Query INSERT IGNORE INTO `test`.`deadlocks` (`server`,`ts`,`thread`,`txn_id`,`txn_time`,`user`,`hostname`,`ip`,`db`,`tbl`,`idx`,`lock_type`,`lock_mode`,`wait_hold`,`victim`,`query`) VALUES ('192.168.60.159','2015-09-30T09:44:21','1694','0','20','root','localhost','','test','t1','PRIMARY','RECORD','X','w','0','delete from t1 where id=1') /* pt-deadlock-logger */
1735 Query INSERT IGNORE INTO `test`.`deadlocks` (`server`,`ts`,`thread`,`txn_id`,`txn_time`,`user`,`hostname`,`ip`,`db`,`tbl`,`idx`,`lock_type`,`lock_mode`,`wait_hold`,`victim`,`query`) VALUES ('192.168.60.159','2015-09-30T09:44:21','1695','0','11','root','localhost','','test','t','PRIMARY','RECORD','X','w','1','delete from t where id=1') /* pt-deadlock-logger */
1735 Query commit=
可以看到,再次执行的时候,会检测DEADLOCKS表是否存在,如果不存在会建立(因为有--create-dest-table参数),并且会将之前的死锁信息以INSERT IGNORE的方式插入到deadlocks表中。
例3、监控死锁信息,只输出终端,总共监控12s,每ss采集一次数据
[root@slave159 test]# pt-deadlock-logger --interval=3s --run-time=12s u=huang,p=huang,P=3306,h=192.168.60.159
server ts thread txn_id txn_time user hostname ip db tbl idx lock_type lock_mode wait_hold victim query
192.168.60.159 2015-09-30T09:44:21 1694 0 20 root localhost test t1 PRIMARY RECORD X w 0 delete from t1 where id=1
192.168.60.159 2015-09-30T09:44:21 1695 0 11 root localhost test t PRIMARY RECORD X w 1 delete from t where id=1
<pre name="code" class="html"><pre name="code" class="html"><pre name="code" class="html">查询日志中的相关信息:
150930 10:39:18 1810 Connect huang@192.168.60.159 on
1810 Query set autocommit=1
1810 Query SELECT @@SQL_MODE
1810 Query SHOW VARIABLES LIKE 'wait\_timeout'
1810 Query SET SESSION wait_timeout=10000
1810 Query SET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION'*/
1810 Query SELECT @@server_id /*!50038 , @@hostname*/
1810 Query SELECT CONCAT(@@hostname, @@port)
1810 Query SHOW /*!40100 ENGINE*/ INNODB STATUS /* pt-deadlock-logger */
150930 10:39:21 1810 Query SHOW /*!40100 ENGINE*/ INNODB STATUS /* pt-deadlock-logger */
150930 10:39:24 1810 Query SHOW /*!40100 ENGINE*/ INNODB STATUS /* pt-deadlock-logger */
150930 10:39:27 1810 Query SHOW /*!40100 ENGINE*/ INNODB STATUS /* pt-deadlock-logger */
150930 10:39:30 1810 Quit
可以从查询日志看到,每隔3s发出一次SHOW /*!40100 ENGINE*/ INNODB STATUS命令,总共发了4个这样的命令。和上面给的参数符合。 需要注意的是,这个工具只收集的是INNODB的死锁信息。
--help
[root@slave159 test]# pt-deadlock-logger --help
pt-deadlock-logger logs information about MySQL deadlocks on the given DSN.
Information is printed to C<STDOUT>, and it can also be saved to a table by
specifying L<"--dest">. The tool runs for forever unless L<"--run-time"> or
L<"--iterations"> is specified. For more details, please use the --help option,
or try 'perldoc /usr/local/bin/pt-deadlock-logger' for complete documentation.
Usage: pt-deadlock-logger [OPTIONS] DSN
Options:
--ask-pass Prompt for a password when connecting to MySQL
--charset=s -A Default character set
--clear-deadlocks=s Use this table to create a small deadlock
--columns=A The columns are: (default server, ts, thread, txn_id,
txn_time, user, hostname, ip, db, tbl, idx, lock_type,
lock_mode, wait_hold, victim, query)
--config=A Read this comma-separated list of config files; if
specified, this must be the first option on the command
line
--create-dest-table Create the table specified by --dest
--daemonize Fork to the background and detach from the shell
--database=s -D Connect to this database
--defaults-file=s -F Only read mysql options from the given file
--dest=d DSN for where to store deadlocks; specify at least a
database (D) and table (t)
--help Show help and exit
--host=s -h Connect to host
--interval=m How often to check for deadlocks (default 30).
Optional suffix s=seconds, m=minutes, h=hours, d=days;
if no suffix, s is used.
--iterations=i How many times to check for deadlocks
--log=s Print all output to this file when daemonized
--numeric-ip Express IP addresses as integers
--password=s -p Password to use when connecting
--pid=s Create the given PID file
--port=i -P Port number to use for connection
--quiet Do not deadlocks; only print errors and warnings to
STDERR
--run-time=m How long to run before exiting. Optional suffix s=
seconds, m=minutes, h=hours, d=days; if no suffix, s is
used.
--set-vars=A Set the MySQL variables in this comma-separated list of
variable=value pairs
--socket=s -S Socket file to use for connection
--tab Use tabs to separate columns instead of spaces
--user=s -u User for login if not current user
--version Show version and exit
--[no]version-check Check for the latest version of Percona Toolkit, MySQL,
and other programs (default yes)
Option types: s=string, i=integer, f=float, h/H/a/A=comma-separated list, d=DSN, z=size, m=time
Rules:
This tool accepts additional command-line arguments. Refer to the SYNOPSIS and usage information for details.
DSN syntax is key=value[,key=value...] Allowable DSN keys:
KEY COPY MEANING
=== ==== =============================================
A yes Default character set
D yes Default database
F yes Only read default options from the given file
P yes Port number to use for connection
S yes Socket file to use for connection
h yes Connect to host
p yes Password to use when connecting
t no Table in which to store deadlock information
u yes User for login if not current user
If the DSN is a bareword, the word is treated as the 'h' key.
Options and values after processing arguments:
--ask-pass FALSE
--charset (No value)
--clear-deadlocks (No value)
--columns server,ts,thread,txn_id,txn_time,user,hostname,ip,db,tbl,idx,lock_type,lock_mode,wait_hold,victim,query
--config /etc/percona-toolkit/percona-toolkit.conf,/etc/percona-toolkit/pt-deadlock-logger.conf,/root/.percona-toolkit.conf,/root/.pt-deadlock-logger.conf
--create-dest-table FALSE
--daemonize FALSE
--database (No value)
--defaults-file (No value)
--dest (No value)
--help TRUE
--host (No value)
--interval 30
--iterations (No value)
--log (No value)
--numeric-ip FALSE
--password (No value)
--pid (No value)
--port (No value)
--quiet FALSE
--run-time (No value)
--set-vars
--socket (No value)
--tab FALSE
--user (No value)
--version FALSE
--version-check TRUE
[root@slave159 test]#