Wrong/Improvable recommendations

[tyronm]

As mentioned in the questions area I am currently in the process of rewriting the rules file for my needs. I'm just half way through but I've noticed some recommendations that could need improvement, which I would like to pass on to your project as well:

> % query cache used|||<80|||Qcache_free_memory / query_cache_size * 100|||The query cache is not being fully utilized.

This calculates the wrong number, this the % of free query cache, not the % of used qc. It should be 100 - [above expr].

>Sort rows|||=~ /second|minute/|||&hr_bytime(Sort_rows/Uptime_since_flush_status)|||There are lots of rows being sorted. Consider using indexes in more queries to avoid sorting too often.

Sort_rows, from what I was able to read, is also increased when sorting with indexes. Thus, this recommendation is incorrect.

> Query cache size|||eq "0 bytes"|||&hr_bytes(query_cache_size)|||The query_cache is not turned on. It may be useful to turn it on.

Query cache might also be turned off by setting query_cache_type to 'OFF' independent of what query_cache_size is set to.

>MySQL Architecture||| !~ /64/|||"version_compile_machine"|||MySQL is not compiled as a 64-bit package.

From what I was able to read, the 64 bit version of mysql has no or even worse performance. The 64 bit version should only be used where the system memory is above 3 GiB, or there will be no gain at all.

>Query cache low memory prunes|||=~ /second|minute/|||&hr_bytime(Qcache_lowmem_prunes/Uptime_since_flush_status)|||Increase query_cache_size -- there are too many low memory prunes.

Maybe this recommendation should note that a very big query cache (3 digit Mb) might cause serious slow down because of the overhead to manage the cache. So increasing it too much will be bad too.

>temp table rate|||=~ /second|minute/|||&hr_bytime(Created_tmp_tables/Uptime_since_flush_status)|||Too many intermediate temporary tables are being created; consider increasing sort_buffer_size (sorting), read_rnd_buffer_size (random read buffer, ie, post-sort), read_buffer_size (sequential scan).

I couldn't find any source on the internet that suggests a direct relation to temporary tables and any of these variables. Several independent Blog entries suggest (http://ronaldbradford.com/blog/more-on-understanding-sort_buffer_size-2010-05-10/ and http://www.xaprb.com/blog/2010/05/09/how-to-tune-mysqls-sort_buffer_size/) that sort_buffer_size should be left as it is. And increasing read_buffer_size is only suggested when there are a lot of table scans (http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_read_buffer_size and other sources) though setting it too high is bad too (http://www.mysqlperformanceblog.com/2007/09/17/mysql-what-read_buffer_size-value-is-optimal/).