Wrong/Improvable recommendations

Bug #819627 reported by tyronm
This bug affects 1 person
Affects Status Importance Assigned to Milestone

Bug Description

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/).

tyronm (tyronx)
description: updated
To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.