Use Percona Toolkit in MySQL to monitor deadlocks

Percona is a branch of mysql. I believe many of my friends are familiar with it. percona-toolkit is a toolkit launched by percona. It contains a lot of useful and useful mysql maintenance kits, I started to learn about this tool today. The first thing I want to know is how to check deadlocks.

If you use the traditional deadlock check method, you can use the show engine innodb status method, but this method shows the latest deadlock. If there are a large number of deadlocks, it is not easy to see clearly;

In this case, you can use the percona-toolkit tool in pt-deadlock-logger;

Basic usage:

Pt-deadlock-logger u = user, p = password, h = host

That is, the username, password, and host. When using this tool, it usually runs for a period of time to check,

For example, you can specify the parameter -- run-time. You can also specify to save the result to a file (-- log parameter)

Or keep it in the table (-- dest parameter). The following is an example:

Pt-deadlock-logger -- create-dest-table -- dest D = test, t = deadlocks u = root, h = 127.0.0.1

In this case, a test. deadlocks table is created by default. Check the following structure:

Mysql> show create table test. deadlocks \ G

* *************************** 1. row ***************************

Table: deadlocks
Create Table: create table 'destlocks '(
'Server' char (20) not null,
'Ts' datetime not null,
'Thread' int (10) unsigned not null,
'Txn _ id' bigint (20) unsigned not null,
'Txn _ time' smallint (5) unsigned not null,
'User' char (16) not null,
'Hostname' char (20) not null,
'IP' char (15) 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 (3) unsigned not null,
'Query' text not null,
Primary key ('server', 'ts', 'thread ')
) ENGINE = InnoDB default charset = latin1

At this time, you can start monitoring:
# Pt-deadlock-logger -- daemonize -- run-time = 3000 -- dest D = test, t = deadlocks u = root, h = 127.0.0.1
Then, the deadlock result is saved in the table as follows:
Mysql> select * from test. deadlocks \ G
* *************************** 1. row ***************************
Server: 127.0.0.1
Ts: 12:13:36
Thread: 32
Txn_id: 0
Txn_time: 113
User: root
Hostname: localhost
Ip:
Db: world
Tbl: City
Idx: Name
Lock_type: RECORD
Lock_mode: X
Wait_hold: w
Victim: 0
Query: update City SET name = 'new york2 'where name = 'New York'
* *************************** 2. row ***************************
Server: 127.0.0.1
Ts: 12:13:36
Thread: 33
Txn_id: 0
Txn_time: 110
User: root
Hostname: localhost
Ip:
Db: world
Tbl: City
Idx: Name
Lock_type: RECORD
Lock_mode: X
Wait_hold: w
Victim: 1
Query: update City set Name = 'seattle2 'where name = 'seattle'

Here we can clearly see that there are two records that lead to deadlocks, and the type of locks is quite clear. Please mention,
In mysql 5.6, innodb_print_all_deadlocks this setting can see the deadlock situation, very convenient, refer to the http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_print_all_deadlocks

Percona Toolkit: click here
Percona Toolkit: click here