Currently running on a 16GB system - Ubuntu 64 bit. INnodb Buffer Pool is set to 10GB.

tuning-primer shows the following:

Query cache is enabled
Current query_cache_size = 512 M
Current query_cache_used = 501 M
Current query_cache_limit = 4 M
Current Query cache Memory fill ratio = 97.87 %
Current query_cache_min_res_unit = 4 K
However, 9418108 queries have been removed from the query cache due to lack of memory
Perhaps you should raise query_cache_size

That is over 9million queries removed. System uptime is 8 days. Should I remove the Query Cache altogether? Our db is always under heavy I/O.



Oh yes, by all means, please disable the query cache (set query_cache_size = 0). Why ???

The Query Cache will always butt heads with InnoDB. It would be nice if InnoDB's MVCC would let queries be served from the query cache if modifications do not affect repeatable reads for other transactions. Unfortunately, it does not do that.

Apparently, you have a lot of queries that get invalidated rather quickly and are not reused.

For InnoDB under MySQL 4.0, the query cache was disabled for transactions. For MySQL 4.1+, InnoDB plays traffic cop when allowing access to the query cache on a per-table basis.

For more information on how this is actually performed, please read pages 213-215 of the book "High Performance MySQL (Second Edition)".

  • Thanks Rolando, you have answered a few of my pervious questions so far I wish I had enough rep to give u an upvote. BTW just checking, that "Why???" wasn't out of sarcasm - seemed like it before I read the rest of the paragraph :) – Tom C Aug 29 '12 at 19:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.