MySQL Optimization – FLUSH QUERY CACHE Every 6 Hours

cacheMySQLoptimization

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

Best Answer

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?)

Related Solutions

Should an Index Be Added for a Monthly Query in MySQL?

Here is one sure way to know if an index is worth it:

Run these queries:

SELECT type,COUNT(1) indtypecount FROM users GROUP BY type WITH ROLLUP; 
SELECT COUNT(DISTINCT type) typecount FROM users GROUP BY type WITH ROLLUP;

This query will give you the count of each type (indtypecount aka individual typecount) and the table count as the last row.

If indtypecount is more than 5% of the rowcount of the whole table, the MySQL Query Optimizer will ignore any index no matter how many times you run ANALYZE TABLE.

If your table is huge and the indtypecount for a type is less than 5%, an index scan is better than a full table scan anyday.

If typecount (cardinality of type) is something ridiculously small, this is all a moot point. Indexing values such as gender (M,F), day of week, active status, and marital status would produce indexes that resemble monster link lists of types that the MySQL Query Optimizer would find totally useless and, thus, space wasted.

If the running time of once-a-month queries is not a concern, indexing or not is strictly your personal choice.

PostgreSQL Cache Misses – How to Identify Queries Causing Them

The pg_stat_statements extension does exactly what you want, giving the block hits and misses for each statement.

However, I usually don't find this information all that useful. Many of the block misses are actually served by the file system cache and not actually read from disk. PostgreSQL provides no direct way to discern these types of misses.

I think the best thing to do would be to turn on track_io_timing and then look at the time spent servicing those misses, rather than the raw number of misses.

Best Answer

EPILOGUE

Related Solutions

Should an Index Be Added for a Monthly Query in MySQL?

PostgreSQL Cache Misses – How to Identify Queries Causing Them

Related Question