Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1019943
  • 博文数量: 646
  • 博客积分: 288
  • 博客等级: 二等列兵
  • 技术积分: 5375
  • 用 户 组: 普通用户
  • 注册时间: 2010-07-08 14:33









2012-07-24 16:25:37

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

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


$ 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


$ 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



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 

  --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:
  ===  ====  =============================================
  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
阅读(555) | 评论(0) | 转发(0) |