Mysql – How to automatically Reset Query Cache for improve table cache hit in MySQL

MySQL

I have a VPS running CentOS 6.4 and 2.5GB RAM, Intel(R) Xeon(R) CPU. Its MySQL version is Server version: 5.5.35-cell. I have two active wordpress sites and two inactive sites (inactive sites use for some test and one for personal use) in this server.

This VPS MySQL configuration file has following query cache.

thread_cache_size = 16

query_cache_type = 1
query_cache_size = 30M
query_cache_limit = 1M

After 30 minutes left, my table cache hit rate goes to the lower level. After 1h 49m 0s MySQL up-time, it's 4% (334 open / 7K opened).

I think to overcome this issue, I have to reset query cache, rather than the flush query cache. Therefore, how do I automatically Reset Query Cache for optimize MySQL table caching?

Best Answer

The query cache is only helpful for low write, high read workloads and should be pretty handy on a blog with this access pattern. If you issue any DML then you invalidate the QC for that table and repopulation would be necessary. Have you calculated the QC efficiency based on your local status?

http://www.mysqlperformanceblog.com/2006/07/27/mysql-query-cache/

The table cache is a different animal. It caches tables in memory so that MySQL can avoid the overhead of opening them. There's a recipe for tuning this, which is something along the lines of depth of longest join * max_connections but I doubt this us causing you trouble. It's likely that you would be better off tuning the key_buffer (MyISAM's cache for indexes) or the Innodb_buffer_pool_size (InnoDB cache for data + indexes) to reduce load on the server.

http://www.mysqlperformanceblog.com/2009/11/16/table_cache-negative-scalability/

Related Question