MySQL Bugs: #61111: Query Cache Has Sub-Optimal Hit Rate has false positives on standby slave

Bug #61111	Query Cache Has Sub-Optimal Hit Rate has false positives on standby slave
Submitted:	10 May 2011 7:28	Modified:	20 May 2011 23:17
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.3	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Query Cache Has Sub-Optimal Hit Rate (v1.94) reports critical events on a standby slave.

1. The query cache hitrate on the slave is really bad. This is becouse it's a slave which almost doesn't get any queries.

mysql> show global status like 'Q%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| Qcache_free_blocks      | 1         |
| Qcache_free_memory      | 268407800 |
| Qcache_hits             | 1         |
| Qcache_inserts          | 7         |
| Qcache_lowmem_prunes    | 0         |
| Qcache_not_cached       | 57658     |
| Qcache_queries_in_cache | 1         |
| Qcache_total_blocks     | 4         |
| Queries                 | 718775    |
| Questions               | 90952     |
+-------------------------+-----------+
10 rows in set (0.00 sec)

How to repeat:
Schedule the rule on a slave which almost doesn't get any queries.

Suggested fix:
Possible fixes:

1. Unschedule this rule on standby slaves and schedule again after a failover. We don't want to do this as it's to easy to forget and this is also something we don't want to worry about in case of a failover.

2. Modify the rule to only raise alerts if the number of queries is high and/or Qcache_inserts and Qcache_hits are high enough.

I think that this rule will also give false positives on a master with a high insert rate.

And turning on the query cache could be really bad for performance...even if the hitrate is high.
http://dom.as/tech/query-cache-tuner/
http://www.mysqlperformanceblog.com/2006/07/27/mysql-query-cache/

Thank you for the problem report.

Added to 2.3.4 changelog:

The Query Cache Has Sub-Optimal Hit Rate rule was updated to avoid
false positives on systems with little query activity. The rule only
triggers an alert when a significant amount of data is held in the
query cache memory.