title: MySQL query caching: limited to a maximum cache size of 128 MB?
body: <p>My application is very database intensive so I've tried really hard to make sure the application and the MySQL database are working as efficiently as possible together.</p>
  
  <p>Currently I'm tuning the MySQL query cache to get it in line with the characteristics of queries being run on the server.</p>
  
  <p><code>query_cache_size</code> is the maximum amount of data that may be stored in the cache and <code>query_cache_limit</code> is the maximum size of a single resultset in the cache.</p>
  
  <p>My current MySQL query cache is configured as follows:</p>
  
  <pre><code>query_cache_size=128M
  query_cache_limit=1M
  </code></pre>
  
  <p><code>tuning-primer.sh</code> gives me the following tuning hints about the running system:</p>
  
  <pre><code>QUERY CACHE
  Query cache is enabled
  Current query_cache_size = 128 M
  Current query_cache_used = 127 M
  Current query_cache_limit = 1 M
  Current Query cache Memory fill ratio = 99.95 %
  Current query_cache_min_res_unit = 4 K
  However, 21278 queries have been removed from the query cache due to lack of memory
  Perhaps you should raise query_cache_size
  MySQL won't cache query results that are larger than query_cache_limit in size
  </code></pre>
  
  <p>And <code>mysqltuner.pl</code> gives the following tuning hints:</p>
  
  <pre><code>[OK] Query cache efficiency: 31.3% (39K cached / 125K selects)
  [!!] Query cache prunes per day: 2300654
  
  Variables to adjust:
      query_cache_size (&gt; 128M)
  </code></pre>
  
  <p>Both tuning scripts suggest that I should raise the <code>query_cache_size</code>. However, increasing the <code>query_cache size</code> over 128M may reduce performance according to <code>mysqltuner.pl</code> (see <a href="http://mysqltuner.pl/" rel="noreferrer">http://mysqltuner.pl/</a>).</p>
  
  <p>How would you tackle this problem? Would you increase the query_cache_size despite <code>mysqltuner.pl</code>'s warning or try to adjust the querying logic in some way? Most of the data access is handled by Hibernate, but quite a lot of hand-coded SQL is used in the application as well.</p>