Logging Deadlock errors

The principal source of information for InnoDB diagnostics is the output of SHOW ENGINE INNODB STATUS but there are some sections that are not very useful. For example, LATEST DETECTED DEADLOCK only shows, as the name implies, the latest error detected. If you have 100 deadlocks per minute you will be able to see only the latest one and that is not very helpful when you are debugging your application code. Continuing my series of blog posts about Percona Toolkit, in this case I’m going to talk about pt-deadlock-logger.

pt-deadlock-logger

The tool is very simple and useful. It monitors the output of SHOW ENGINE INNODB STATUS and log the new deadlocks to a file or to a table that we can later review. Let’s see how it works with an example.

The basic command to use is:

Without any more options the tool will start to show on STDOUT the deadlocks that are happening on our database. This tool is usually run for a long period of time that you can specify with –run-time so sometimes make sense to store the information on a file (–log) or on a table (–dest) for later review. In this example we are going to store the deadlocks information on a table.

First we have to create the destination table:

Now, we can start to log the deadlocks in our database:

After some time, we can check the deadlock information in our table:

We can see the two queries that caused the deadlock with the information like user, hostname, thread id and which one was the victim of the deadlock. You can group by server and timestamp to get all events that correspond to the same deadlock.

Another trick, with infrastructures of more than one server is possible to use the tool to have a central repository with all the deadlocks. We just need to run the tool on every node and configure it to store logs on a centralized database.

In MySQL 5.6 will be possible to log all deadlocks on error log using innodb_print_all_deadlocks parameter.

Share this post

Comments (8)

  • sbester

    MySQL 5.6 logs all deadlocks but I think it’s still in an unstructured format?
    ( http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_print_all_deadlocks )

    September 19, 2012 at 9:42 am
  • Miguel Angel Nieto

    sbester, good one. I didn’t know about that parameter on 5.6. Seems that it logs the deadlocks on error log with the same format that uses for show engine innodb. I’m going to update the blog post.

    September 19, 2012 at 9:46 am
  • Cédric

    MONyog also logs deadlocks : http://www.webyog.com/en/monyog_feature_list.php
    “pt-deadlock-logger” is a nice tool and that works but what bothers me a bit (just a bit) with the “–daemonize” option of the Percona Toolkit is the need to ensure that the process run all the time (don’t forget to relaunch the tool after a server reboot for example).

    September 20, 2012 at 2:07 am
  • Ricardo

    This is not working in Ubuntu 12.04, getting a perl error:
    Argument “” isn’t numeric in numeric comparison () at /usr/bin/pt-deadlock-logger line 2145.

    March 6, 2013 at 8:29 pm
  • Colin

    Here is a script that will record full deadlock details in separate files: https://gist.github.com/colinmollenhour/6578ac005074bd2f992e

    July 10, 2014 at 6:44 pm
  • Sumithra

    installed percona-toolkit and started pt-deadlock-logger as a deamon, it doesnt seem to log the deadlocks in DB.

    December 19, 2017 at 10:31 am
  • Sumithra

    I installed percona-toolkit and started pt-deadlock-logger as a deamon, it doesnt seem to log the deadlocks in DB. But we do see deadlocks when I execute ” show engine innodb status”.
    Please help me on this. I have configured the /etc/percona-toolkit/pt-deadlock-logger.conf with host, socket, username and password.

    December 19, 2017 at 10:33 am
  • Roman Borysenko

    Should pt-deadlock-logger be run with Cron to get deadlocks info constantly or --daemonize option would be of help?

    January 18, 2018 at 7:57 am

Comments are closed.

Use Percona's Technical Forum to ask any follow-up questions on this blog topic.