Bug #46768 Table lock contention rules have inconsistent thresholds

• Submitted: 17 Aug 2009 21:39
• Modified: 18 Sep 2009 16:48
• Reporter: Andy Bang
• Status: Closed
• Category: MySQL Enterprise Monitor: Advisors/Rules
• Severity: S3 (Non-critical)
• Version: 2.1.0.1093
• OS: Any
• Assigned to: Andy Bang
• CPU Architecture: Any

[17 Aug 2009 21:39] Andy Bang

Description:
So in lock_contention_excessive (Basic), we have this expression:

  <expression><![CDATA[(%Uptime% > 10800) && (((%Table_locks_waited% / (%Table_locks_immediate% + %Table_locks_waited%)) * 100) > THRESHOLD)]]></expression>

With thresholds of 1/3/5 (for Info/Warning/Critical).

And in table_lock_contention_excessive (Platinum), we have the *same* expression:

  <expression><![CDATA[(%Uptime% > 10800) && (((%Table_locks_waited% / (%Table_locks_immediate% + %Table_locks_waited%)) * 100) > THRESHOLD)]]></expression>

But with thresholds of 30/60/95.

How to repeat:
Load a Platinum bundle.
Look at the two rules listed above.

Suggested fix:
Make them consistent.

[17 Aug 2009 21:46] Enterprise Tools JIRA Robot

Andy Bang writes: 
We use hit ratios everywhere else rather than miss ratios - so should convert that to:

<expression><![CDATA[(%Uptime% > 10800) && (100 - ((%Table_locks_waited% / (%Table_locks_immediate% + %Table_locks_waited%)) * 100) > THRESHOLD)]]></expression>

Then move the Platinum thresholds over to Basic.

[17 Aug 2009 23:30] Enterprise Tools JIRA Robot

Andy Bang writes: 
Pushed up to revision 299.

[18 Aug 2009 22:14] Enterprise Tools JIRA Robot

Andy Bang writes: 
Updated thresholds for table lock rules based on feedback from Mark Leith:

Info is now 99
Warning is now 90
Critical is removed -- there is no longer a Critical threshold for these rules

[24 Aug 2009 22:46] Enterprise Tools JIRA Robot

Keith Russell writes: 
Patch installed in versions => 2.1.0.1096.

[25 Aug 2009 21:26] Enterprise Tools JIRA Robot

Marcos Palacios writes: 
Verified fixed in advisor bundle 2.1.0.1096.

[18 Sep 2009 16:48] Tony Bedford

Added an entry to the 2.1.0 changelog:

Table lock contention rules had inconsistent thresholds.

The rule lock_contention_excessive (part of the Basic subscription) contained the following expression:

(%Uptime% > 10800) && (((%Table_locks_waited% / 
(%Table_locks_immediate% + %Table_locks_waited%)) * 100) > THRESHOLD)

This used thresholds of 1/3/5 (for Info/Warning/Critical).

However, in the rule table_lock_contention_excessive (part of the Platinum subscription), the same expression existed but with different thresholds:

(%Uptime% > 10800) && (((%Table_locks_waited% /
(%Table_locks_immediate% + %Table_locks_waited%)) * 100) > THRESHOLD)

In this case the thresholds were 30/60/95.