How to check number of innodb deadlocks that have occurred
Question: How to Monitor Number of InnoDB Deadlocks?
To log all deadlock information to the mysql error log, you can enable the global variable “innodb_print_all_deadlocks”. However, that is not useful for monitoring via a program or script. So, we’ll use standard SQL queries instead.
To see a once-off answer, you can run this query:
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)
We could also create some stored routine that checks the INNODB_METRICS table every few seconds and prints the value when it changed. For example:
drop procedure if exists `track_metric`;
delimiter $
create procedure `track_metric`(`p_name` varchar(193) charset utf8)
begin
declare `v_count` bigint unsigned default 0;
declare `v_old_count` bigint unsigned default 0;
set global innodb_monitor_enable=`p_name`;
repeat
select `COUNT` from `information_schema`.`INNODB_METRICS` where `NAME`=p_name into v_count;
if `v_old_count` <> `v_count` then select now(),`p_name` as `metric`,`v_count` as `count`; end if;
set `v_old_count`:=`v_count`;
do sleep(1);
until 1=2 end repeat;
end $
delimiter ;
call `track_metric`('lock_deadlocks');
The output may look like this, changing only when new deadlocks occur:
mysql> call `track_metric`('lock_deadlocks');
+---------------------+----------------+-------+
| now() | metric | count |
+---------------------+----------------+-------+
| 2014-09-29 15:32:41 | lock_deadlocks | 546 |
+---------------------+----------------+-------+
1 row in set (0.00 sec)
+---------------------+----------------+-------+
| now() | metric | count |
+---------------------+----------------+-------+
| 2014-09-29 15:32:44 | lock_deadlocks | 555 |
+---------------------+----------------+-------+
1 row in set (3.01 sec)
+---------------------+----------------+-------+
| now() | metric | count |
+---------------------+----------------+-------+
| 2014-09-29 15:32:51 | lock_deadlocks | 602 |
+---------------------+----------------+-------+
1 row in set (10.02 sec)
+---------------------+----------------+-------+
| now() | metric | count |
+---------------------+----------------+-------+
| 2014-09-29 15:34:31 | lock_deadlocks | 607 |
+---------------------+----------------+-------+
1 row in set (1 min 50.09 sec)