MySQL Bugs: #74131: Please add a counter of InnoDB deadlocks

Bug #74131	Please add a counter of InnoDB deadlocks
Submitted:	29 Sep 2014 9:39	Modified:	29 Sep 2014 13:01
Reporter:	Simon Mudd (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:	5.6.15	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	counter, deadlock

Description:
MySQL 5.6 has information in SHOW ENGINE INNODB STATUS about the latest deadlock.
What it doesn't have is a counter for this type of problem. While not frequent it would be good to have such a counter so that it can be graphed and also a larger number (than normal) of these deadlocks could be more easily detected.

How to repeat:
Look at the SHOW ENGINE INNODB STATUS output and see it has a section:

------------------------
LATEST DETECTED DEADLOCK
------------------------

there are no counters for this type of "event"

Suggested fix:
Add a GLOBAL STATUS variable such as innodb_deadlocks_detected which can be used for monitoring this.

On 5.6 we have this;

mysql> select * from information_schema.innodb_metrics where name='lock_deadlocks'\G
*************************** 1. row ***************************
           NAME: lock_deadlocks
      SUBSYSTEM: lock
          COUNT: 1168
      MAX_COUNT: 1168
      MIN_COUNT: NULL
      AVG_COUNT: 4.728744939271255
    COUNT_RESET: 1168
MAX_COUNT_RESET: 1168
MIN_COUNT_RESET: NULL
AVG_COUNT_RESET: NULL
   TIME_ENABLED: 2014-09-29 12:04:46
  TIME_DISABLED: NULL
   TIME_ELAPSED: 247
     TIME_RESET: NULL
         STATUS: enabled
           TYPE: counter
        COMMENT: Number of deadlocks
1 row in set (0.00 sec)

Shane, thanks for pointing this out. innodb_metrics is a table I often forget to look at partly as there are still global status settings too and this is somewhat confusing.

That said the information I need is indeed there, so sorry for the noise.

I honestly feel the innodb_metrics table doesn't get enough appreciation 
(even from those in Support).

Closing the SR, as information is in:

https://dev.mysql.com/doc/refman/5.6/en/innodb-metrics-table.html