Mysql管理必备工具Maatkit详解之五（mk-deadlock-logger）
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 localhosttest c GEN_CLUST_INDEX RECORD X w 1 select * from c for update
localhost 2007-03-08T20:34:22 83 21310 19 baron localhosttest 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
--collapse -cCollapse whitespace in queries to a single space
--columns -COutput only this comma-separated list of columns
--daemonize Fork and run in the background; POSIX OSes only
--dest -dDSN for where to store deadlocks
--help Show this help message
--interval -iHow often to check for deadlocks (default 0s).Optional
suffix s=seconds, m=minutes, h=hours, d=days; if no suffix,
s is used.
--numip -nExpress IP addresses as integers
--print -pPrint results on standard output
--setvars Set these MySQL variables (default wait_timeout=10000)
--source -sDSN to check for deadlocks; required
--tab -tPrint tab-separated columns, instead of aligned
--time -mHow 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:
KEYCOPYMEANING
====================================================
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
本文来自ChinaUnix博客，如果查看原文请点：http://blog.chinaunix.net/u3/111930/showart_2184839.html
页:
[1]