Is it normal that I have to FLUSH QUERY CACHE once every 6 hours to maintain fast queries?

Question

I am running MySQL 5.5.37. I have a cronjob set to run every 6 hours to flush the query cache. I realized that if I don't do that, then the cache becomes fragmented and average query response time could double or triple. 95% of my data is saved in MyISAM tables.

My average response time is between 15ms to 30ms as long as I have the cronjob run.

Is this normal behavior? Or does it indicate that I have an underlying issue that should be fixed? Perhaps my query_cache_min_res_unit value is too low, which is causing fragmentation? Or is fragmentation unavoidable and flushing the cache is perfectly normal?

QUERY CACHE
Query cache is enabled
Current query_cache_size = 4.08 G
Current query_cache_used = 1.05 G
Current query_cache_limit = 3.24 G
Current Query cache Memory fill ratio = 25.79 %
Current query_cache_min_res_unit = 4 K

Answer 1

Flushing the query cache would be OK depending on Storage Engine

InnoDB spends time micromanaging data changes. This includes crosschecking the query cache.

I have a 2-year-old post (Sep 05, 2012 : Is the overhead of frequent query cache invalidation ever worth it?) where I recommend running FLUSH QUERY CACHE via the crontab.

I also have more recent posts

• Jun 07, 2014 : Why query_cache_type is disabled by default start from MySQL 5.6?
• Jun 30, 2014 : Optimize MySQL query_cache_min_res_unit

explaining how one could try to set query_cache_size and query_cache_min_res_unit if one truly knows the data, the average size of a result set, and how many entries should be in the query cache.

Other than having this foreknowledge about the data, fragmentation is unavoidable.

On the flipside, if all your data is MyISAM, you should take the time to set set query_cache_size and query_cache_min_res_unit by getting to know your data. After all, MyISAM does not cache data. It only caches indexes.

EPILOGUE

Regardless of Storage Engine, running FLUSH QUERY CACHE; bypasses the need to set query_cache_size and query_cache_min_res_unit. It quickly eliminates fragmentation issues. If your dataset is not very large, make the time to size your data and result sets. You could eliminate the crontab.

What would be an even greater advantage would be the following

• tune the queries
• add proper indexes to support queries
• tune MyISAM
  • If the MyISAM tables have VARCHAR columns, change row format to FIXED.
  • Make sure the key_buffer_size can accommodate all MyISAM indexes (See What are the main differences between InnoDB and MyISAM?)