MySQL Bugs: #49000: Export number of rollbacks, long lock waits and deadlocks from InnoDB

Bug #49000	Export number of rollbacks, long lock waits and deadlocks from InnoDB
Submitted:	23 Nov 2009 20:20	Modified:	28 Oct 2010 8:38
Reporter:	Mark Callaghan	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: InnoDB Plugin storage engine	Severity:	S4 (Feature request)
Version:	5.0,5.1,5.1 plugin	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	innodb, rollback, STATUS

Description:
I want the number of rollbacks, long lock waits and deadlocks report in SHOW STATUS for InnoDB. The rollback count should include those requested by clients and force by InnoDB. SHOW STATUS reports Innodb_row_lock_waits for all row lock waits, but there is not counter for long lock waits. 

How to repeat:
run SHOW STATUS

Suggested fix:
add counters to SHOW STATUS output

Hi Mark,

What do you mean by long lock wait?  Do you mean any waits above a certain time, like long_query_time and the slow log?

Handler_rollback contains info about client rollbacks, but I could see the usefulness of the automatic ones being logged as well.

And certainly the number of deadlocks detected could be very useful as a feature.

For long lock waits, waits that become an error because the innodb long row lock wait limit has been reached.

long lock wait means that innodb_lock_wait_timeout has been exceeded

Ah okay, that makes good sense to me!

I forgot I published code for this:
http://bazaar.launchpad.net/~mysqlatfacebook/mysqlatfacebook/trunk/revision/33

In fact, we already had following metrics counters for related information:

lock_deadlock_count     
lock_timeout           
trx_num_abort
trx_purge_delay
 
for lock deadlock, lock timeout and rollbacks. Need to check if trx_purge_delay is the same as trx_purge_pending.

The innodb_purge_pending is represented by trx_rseg_history_len.

This bug is marked as a duplicate of a new feature (metrics counters) we have worked on. Our implementation will be in the next 5.6 release.

For new InnoDB server internal stats, we will try to expose them through the new metrics counter interfaces. And all existing status variables will be added as metrics counters.

And to avoid duplicate of information, the new request for counter related information will not be added as system status variables as well. So if the counter is already added as metrics counter, we will not add a new status variable for it.

structure table

Attachment: __always be cool table__.sql (application/octet-stream, text), 5.90 KiB.