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，很簡單的命令，查看最后出現(xiàn)的死鎖的信息。當(dāng)然，你也可以指定想看的信息：
$ 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
第二種方法是把信息儲存到指定的表內(nèi)。首先創(chuàng)建這個表：
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小時內(nèi)每30秒檢查1次。
mysql5.0以后，出現(xiàn)死鎖的情況是越來越少了，如果不是innodb引擎。出現(xiàn)死鎖的情況，特別的少見。
OK,還有其他的一些參數(shù)，可以看下幫助文件。
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