3

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?

Improve this question

2 Answers 2

Reset to default
1

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/

Improve this answer
2
  • By flushing the cache, I mean running FLUSH QUERY CACHE which defragments the memory. I do NOT run RESET QUERY CACHE, which is a more class "flush". I can't use memcached at this point, since the code in question is not mine. My innodb_buffer_pool is greater than the total size of my innodb databases. Is there any reason to enlarge it? I'm using mysqltuner and tuning-primer.sh; my problem is that eventually, the cache fills up, and it has to throw away the entire thing (qcache_lowmem_prunes > 0)
    – Mikeage
    Feb 3, 2010 at 9:14
  • You're never going to have a query cache that doesn't have prunes with a non-trivial database size. As activity grows on the db (inserts/updates/deletes) the queries in the cache will clear out more and you'll start to see performance drop again. The most practical way to solve this is by breaking up complicated joins into smaller queries and by implementing external caching.
    – Jon Haddad
    Feb 3, 2010 at 17:20
0

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

Improve this answer
1
  • No, you can't write a trigger on a select. It can only occur when data changes. Read through the link you posted - it only works on UPDATE, INSERT, and DELETE.
    – Jon Haddad
    Feb 3, 2010 at 8:51

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .