MySQL Bugs: #60919: Prepared statements rule gives false positives.

Bug #60919	Prepared statements rule gives false positives.
Submitted:	19 Apr 2011 10:07	Modified:	23 Aug 2011 10:32
Reporter:	Daniël van Eeden	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Enterprise Monitor: Advisors/Rules	Severity:	S3 (Non-critical)
Version:	2.3.2	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Prepared Statements Not Being Used Effectively (v 1.2 *) 

The rule is:
(%Com_stmt_prepare% > 1) && ((100 - ((%Com_stmt_prepare% / (%Com_stmt_execute% + 1)) * 100)) < THRESHOLD)

The advice tells the user to monitor PREPARE and EXECUTE statements.

This rule could generate a critical event even if there are not PREPARE and/or EXECUTE statements.

According to the documentation the monitored variables are monitoring API calls and not SQL statements. There are status variables for SQL prepared statements.
http://dev.mysql.com/doc/refman/5.0/en/server-status-variables.html#statvar_Com_xxx

root@localhost [(none)]> SHOW GLOBAL STATUS LIKE 'Com_stmt%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Com_stmt_close          | 26837912 |
| Com_stmt_execute        | 26837932 |
| Com_stmt_fetch          | 0        |
| Com_stmt_prepare        | 26837962 |
| Com_stmt_reprepare      | 5        |
| Com_stmt_reset          | 22864717 |
| Com_stmt_send_long_data | 0        |
+-------------------------+----------+
7 rows in set (0.00 sec)

root@localhost [(none)]> SHOW GLOBAL STATUS LIKE 'Com_%_sql';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| Com_dealloc_sql | 5     |
| Com_execute_sql | 11    |
| Com_prepare_sql | 7     |
+-----------------+-------+
3 rows in set (0.00 sec)

How to repeat:
Schedule this rule.

Suggested fix:
Change rule from:
(%Com_stmt_prepare% > 1) && ((100 - ((%Com_stmt_prepare% / (%Com_stmt_execute% + 1)) * 100)) < THRESHOLD)

To:
(%Com_prepare_sql% > 1) && ((100 - ((%Com_prepare_sql% / (%Com_execute_sql% + 1)) * 100)) < THRESHOLD)

Alternative fix:
Update the advice to match the rule.

Thank you for the problem report.

Added to 2.3.4 changelog:

The "Prepared Statements Not Being Used Effectively" rule was updated
to look at the status variables relating to SQL-based prepared
statements (PREPARE, EXECUTE, and so on) rather than API-based (for
example, mysql_stmt_prepare()) status counters.

Thank you for your bug report. This issue has already been fixed in the latest released version of that product, which you can download at

  http://www.mysql.com/downloads/