Bug #44005 Request for UNDO LOG ENTRIES advisor and graph
Submitted: 1 Apr 2009 6:26 Modified: 24 Jan 2011 19:11
Reporter: Andrii Nikitin Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Enterprise Monitor: Advisors/Rules Severity:S4 (Feature request)
Version:2.* OS:Any
Assigned to: Mark Leith CPU Architecture:Any

[1 Apr 2009 6:26] Andrii Nikitin 
Description:
Sometimes huge transactions happens or server workload boosts for some time. 
To monitor such cases it would be perfect to see "undo log entries" graph. It may be parsed as sum of numbers from INNODB status (those after "undo log entries"):

...
---TRANSACTION 0 1291, ACTIVE 3775 sec, OS thread id 4476
3878 lock struct(s), heap size 604144, undo log entries 2000000
...

It can be achievable with custom advisors (mysql & awk), but since agent already asks 'SHOW INNODB STATUS' frequently, we should handle all derived parameters at once.

How to repeat:
n\a

Suggested fix:
n\a
[1 Apr 2009 7:55] Andrii Nikitin 
To be more clear:
We need to parse SHOW INNODB STATUS output (I assume it gets collected currently) and find lines like:
"undo log entries X"

Adding numbers from that expressions (it is shown for each active transaction) should be total number of undo log entries in InnoDB logs.

I think there is no way to perform such parsing in SQL and doing it as custom shell command will result in multiple SHOW INNODB STATUS calls (for several graphs), which is not good from performance point of view.

From performance point of view that should be parsed in the same place where SHOW INNODB STATUS called currently.
[4 May 2010 11:23] Enterprise Tools JIRA Robot 
Mark Leith writes: 
I've submitted a patch for the INNODB_TRX table in the InnoDB plugin, which now includes this data for all active transactions:

http://bugs.mysql.com/bug.php?id=53336

"undo log entries" maps to "trx_rows_modified" in the INNODB_TRX table once this is pushed.
[26 Oct 2010 11:49] Enterprise Tools JIRA Robot 
Mark Leith writes: 
Graph now added - "InnoDB Transaction Lock Memory". 

Not adding a rule for this by default.
[15 Nov 2010 20:18] Enterprise Tools JIRA Robot 
Bill Weber writes: 
verified new graph in bundle 2.3.1.2038
[24 Jan 2011 19:11] John Russell 
Added to 2.3.1 change log:

A new graph, InnoDB Transaction Lock Memory, illustrates the number
of undo log entries. This value is derived from the
Information_Schema column INNODB_TRX.TRX_ROWS_MODIFIED.