Mysql管理必备工具Maatkit详解之五（mk-deadlock-logger）

2009年05月20日　作者： 大头刚　

mk-deadlock-logger - 查看mysql的死锁信息。安装方法可以参考。

有2种方式可以查看死锁信息。第一种方法是直接打印出来：

$ mk-deadlock-logger --source u=sg,p=xxxx,h=localhost --print
server ts thread txn_id txn_time user hostname ip db tbl idx lock_type lock_mode wait_hold victim query
localhost 2007-03-08T20:34:22 81 21309 29 baron localhost  test c GEN_CLUST_INDEX RECORD X w 1 select * from c for update
localhost 2007-03-08T20:34:22 83 21310 19 baron localhost  test a GEN_CLUST_INDEX RECORD X w 0 select * from a for update

OK，很简单的命令，查看最后出现的死锁的信息。当然，你也可以指定想看的信息：

$ mk-deadlock-logger --source u=sg,p=xxxx,h=localhost --print -C ts,user,hostname,db,tbl,idx
ts user hostname db tbl idx
2007-03-08T20:34:22 baron localhost test c GEN_CLUST_INDEX
2007-03-08T20:34:22 baron localhost test a GEN_CLUST_INDEX

第二种方法是把信息储存到指定的表内。首先创建这个表：

mysql> use test;
Database changed
 
mysql> CREATE TABLE deadlocks (
    ->    server char(20) NOT NULL,
    ->    ts datetime NOT NULL,
    ->    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;
Query OK, 0 rows affected (0.00 sec)
 
mk-deadlock-logger --source u=sg,p=xxxx,h=localhost --dest D=test,\
t=deadlocks --dest D=test,t=deadlocks --daemonize -m 4h  -i 30s

OK，这条语句就是，把死锁信息存入deadlocks表，并在4小时内每30秒检查1次。
mysql5.0以后，出现死锁的情况是越来越少了，如果不是innodb引擎。出现死锁的情况，特别的少见。

OK,还有其他的一些参数，可以看下帮助文件。

mk-deadlock-logger --help
mk-deadlock-logger extracts and saves information about the most recent deadlock
in a MySQL server.  You need to specify whether to print the output or save it
in a database.  For more details, please use the --help option, or try 'perldoc
mk-deadlock-logger' for complete documentation.

Usage: mk-deadlock-logger --source DSN 

Options:
  --askpass          Prompt for a password when connecting to MySQL
  --[no]collapse -c  Collapse whitespace in queries to a single space
  --columns      -C  Output only this comma-separated list of columns
  --daemonize        Fork and run in the background; POSIX OSes only
  --dest         -d  DSN for where to store deadlocks
  --help             Show this help message
  --interval     -i  How often to check for deadlocks (default 0s).  Optional
                     suffix s=seconds, m=minutes, h=hours, d=days; if no suffix,
                     s is used.
  --numip        -n  Express IP addresses as integers
  --print        -p  Print results on standard output
  --setvars          Set these MySQL variables (default wait_timeout=10000)
  --source       -s  DSN to check for deadlocks; required
  --tab          -t  Print tab-separated columns, instead of aligned
  --time         -m  How long to run before exiting.  Optional suffix s=seconds,
                     m=minutes, h=hours, d=days; if no suffix, s is used.
  --version          Output version information and exit
  Specify at least one of --print or --dest.
  DSN values in --dest default to values from --source.

DSN syntax is key=value[,key=value...]  Allowable DSN keys:
  KEY  COPY  MEANING
  ===  ====  =============================================
  A    yes   Default character set
  D    yes   Database to use
  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    yes   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:
  --askpass          FALSE
  --collapse         FALSE
  --columns          (No value)
  --daemonize        FALSE
  --dest             (No value)
  --help             TRUE
  --interval         0
  --numip            FALSE
  --print            FALSE
  --setvars          wait_timeout=10000
  --source           (No value)
  --tab              FALSE
  --time             (No value)
  --version          FALSE