MySQL Bugs: #41317: Advisors make useless recommendations... (Table scans excessive)

Bug #41317	Advisors make useless recommendations... (Table scans excessive)
Submitted:	9 Dec 2008 8:23	Modified:	4 Jan 2011 20:44
Reporter:	Simon Mudd (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Enterprise Monitor: Advisors/Rules	Severity:	S4 (Feature request)
Version:	2.0.0.7101	OS:	Any
Assigned to:	Andy Bang	CPU Architecture:	Any

Description:
The text recommends:

Advice
Turn on the Slow Query Log and monitor what goes into it. Statements that are logged there are candidates for tuning. Once you have found tuning candidates, use the EXPLAIN statement on the queries to see which tables should have indexes added to them. If you are using MySQL 4.1 or later you can use the --log-queries-not-using-indexes option to log all statements that do a full table scan, even if they would not otherwise qualify for the slow query log.

Yes, the server has the slow log enabled and no entries...

The suggestion about using --log-queries-not-using-indexes should be used with caution on production servers. No mention is made of this.

Also why don't you suggest the use of your own query analysis functionality?

How to repeat:
N/A

Suggested fix:
Please take more care with the recommendations. If necessary link to the mysql.com page to give a more detailed explanation of possible actions and how to do them.

As a further thought perhaps it's also worth thinking for future versions of merlin:
1. to provide a way to adjust the threshold for this and similar rules if the sysadmin considers they are not configured correctly.
2. provide a description of the formula being evaluated. Sometimes it's not really that clear what you are actually evaluating. 

Expression
(%Uptime% > 10800) && (%Handler_read_rnd_next% > 4000) && ((100-(((%Handler_read_rnd_next% + %Handler_read_rnd%) / (%Handler_read_rnd_next% + %Handler_read_rnd% + %Handler_read_first% + %Handler_read_next% + %Handler_read_key% + %Handler_read_prev%))*100)) < THRESHOLD)
Evaluated Expression
(6099377 > 10800) && (6875 > 4000) && ((100-(((6875 + 98) / (6875 + 98 + 782 + 519 + 6331 + 0))*100)) < 60)

I think this is supposed to mean: system has been up for at least 3 hours and ... and 60% of the random reads involve table scans. This is much clearer to understand than the formula which has been configured more for merlin than for the user.

Changed severity to feature request

Thank you for a reasonable feature request.

many rules need calibration.

Mark Leith writes: 
This is now resolved in 2.3 - we point from these advisors directly to QUAN, filtering for all queries that have the no_index_used or no_good_index_used flags set.

Closed without adding a changelog entry, as this is something the customer would see immediately in normal use, and don't think a changelog entry is warranted for general wording or linking changes in recommendations.

(Would need some more details about which advisors are affected if a changelog entry is desired.)