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.
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.