Home / server / Questions / 109027
Accepted
Mikeage
Asked: 2010-02-03 20:31:29 +0800 CST

Tuning mysql's query cache

  • 772

I'm looking into tuning the query cache on mysql on my VPS [a 360MB setup]. When I started, it was set at 32MB, and I was getting a non-trivial amount of qcache_lowmem_prunes. I added a cronjob to run FLUSH QUERY CACHE periodically, which helped, but not enough. I've been enlarging it gradually, but even at 80MB [I temporarily took some memory away from php-cgi], I still see lowmem prunes.

OTOH, I only have about a 50% hit ratio [i.e., Qcache_hits is about half of Qcache_hits+Com_select]. I strongly suspect that many of my queries are one time queries, and therefore should not be cached. I'd like to identify them, and mark them with SQL_NO_CACHE.

Any suggestions for an easy way to do this?

It's also possible that some of my queries are not cached since the tables have changed, although I don't think, based on a rough mental model, that this is the case. At least, the results should be consistant for most queries; I'm using InnoDB, so my understanding is that a random UPDATE does not necessarily invalidate all SELECTs. Is this correct?

mysql innodb

2 Answers

  1. Best Answer

    I don't understand why you want to flush the cache. It takes care of itself, and there's no performance benefit (that I've seen) to periodically flushing it. 50% cache hit ratio is actually pretty amazing.

    Any update to any row in the table invalidates all queries that used that table. That means any queries that join to it are purged. It doesn't matter if the results are affected or not.

    I suggest using a caching solution like memcached instead, where you have more fine grained control of what's being cached and how the data is expired.

    If you're seeing performance problems, you might be better off dropping the memory associated to the query cache and bumping up your innodb_buffer_pool and innodb_additional_mem_pool_size.

    http://dev.mysql.com/doc/refman/5.0/en/innodb-configuration.html

    Also, you should take a look at mysql tuner: https://launchpad.net/mysqltuner/

    • 1

  2. You may create MySQL trigger to analyze on the fly incomming requests and rewrite it with the query_cache var.

    This will consume more CPU, but avoid to look at the source of the software.

    http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html

    • 0