Sunday, November 19, 2006

Is the Query Cache hurting you?

If you are using memcached, you may want to check your query cache hits : cache inserts ratio. On one of my servers I inherited this ratio was 0.35 indicating that having the query cache turned on was actually harmful. I believe at the very least you need to have a ratio of 1. I was chatting with Sheeri and we both were of the opinion that a ratio of 90 was a very healthy ratio. By turning query cache off by default, we can then selectively cache the queries we need.

Later when I was talking to Ronald, he said he really wanted to be able to query the query cache to see what queries are cached. If will indeed be really cool to see that implemented.

So since turning the query cache off, I have been witnessing more throughput as less resources are being spent to maintain the inserts to query cache (which wasn't being effectively used at all).

I wanted to make all of these a little bit more detailed posts but have so little time. However I did want to post something to may be, expand upon, later. Your comments, questions are welcome and appreciated.


jim said...

i am pretty sure i've seen a patch that added something like 'SHOW QUERY CACHE' floating around. maybe something that jeremy cole did?

Mark Leith said...

Jim's right - we had a patch for SHOW QUERY CACHE - quite some time ago.

It was eventually rejected (and not taken any further) because it did not deal with character sets properly.

See this thread: