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?