pt-deadlock-logger

最新推荐文章于 2023-10-10 20:56:33 发布

kf_panda

最新推荐文章于 2023-10-10 20:56:33 发布

阅读量682

点赞数

分类专栏： PerconaToolkit

本文链接：https://blog.csdn.net/gua___gua/article/details/48826415

版权

PerconaToolkit 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

pt-deadlock-logger ：显示指定的DSN的死锁日志信息，他能够标准输出到屏幕也可以把信息写日志文件中（--log参数）甚至可以保留到指定的表中（--dest参数），该工具默认是永久执行，除非指定 --run-time 或 --iterations 。

使用方法

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]#

kf_panda

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
pt-deadlock-logger

pt-deadlock-logger ：显示指定的DSN的死锁日志信息，他能够标准输出到屏幕也可以把信息写日志文件中（--log参数）甚至可以保留到指定的表中（--dest参数），该工具默认是永久执行，除非指定 --run-time 或 --iterations。使用方法pt-deadlock-logger [OPTIONS] DSN常用的参数（详细参数查看--help
复制链接

扫一扫

专栏目录