title: Query cache efficiency
body: <p>I'm using MySQLTuner.pl to optimize my site.... though I'm not entirely sure how to resolve some of these issues and am wondering if someone can help me out.</p>
  
  <p>I'm running 16GB of RAM with the following MySQL settings:</p>
  
  <pre><code>key_buffer              = 1024M
  max_allowed_packet      = 16M
  thread_stack            = 192K
  thread_cache_size       = 8
  
  myisam-recover         = BACKUP
  max_connections        = 1500
  table_cache            = 256
  thread_concurrency     = 4
  
  query_cache_limit       = 2M
  query_cache_size        = 32M
  query_cache_type        = 1
  
  tmp_table_size          = 512M
  max_heap_table_size     = 128M
  join_buffer_size        = 128M
  myisam_sort_buffer_size = 512M
  </code></pre>
  
  <p>Here's the output of my tuner</p>
  
  <pre><code>   -------- General Statistics --------------------------------------------------
  [--] Skipped version check for MySQLTuner script
  [OK] Currently running supported MySQL version 5.1.41-3ubuntu12.6-log
  [OK] Operating on 64-bit architecture
  
  -------- Storage Engine Statistics -------------------------------------------
  [--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
  [--] Data in MyISAM tables: 98M (Tables: 402)
  [--] Data in InnoDB tables: 16K (Tables: 1)
  [!!] Total fragmented tables: 17
  
  -------- Performance Metrics -------------------------------------------------
  [--] Up for: 10s (1K q [132.400 qps], 443 conn, TX: 119K, RX: 82K)
  [--] Reads / Writes: 100% / 0%
  [--] Total buffers: 1.2G global + 130.6M per thread (1500 max threads)
  [!!] Maximum possible memory usage: 192.4G (1225% of installed RAM)
  [OK] Slow queries: 0% (0/1K)
  [OK] Highest usage of available connections: 0% (2/1500)
  [OK] Key buffer size / total MyISAM indexes: 1.0G/72.5M
  [!!] Key buffer hit rate: 72.3% (47 cached / 13 reads)
  [!!] Query cache efficiency: 0.0% (0 cached / 875 selects)
  [OK] Query cache prunes per day: 0
  [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 2 sorts)
  [OK] Temporary tables created on disk: 23% (48 on disk / 201 total)
  [OK] Thread cache hit rate: 99% (2 created / 443 connections)
  [!!] Table cache hit rate: 4% (128 open / 2K opened)
  [OK] Open file limit used: 3% (257/7K)
  [OK] Table locks acquired immediately: 100% (449 immediate / 449 locks)
  [OK] InnoDB data size / buffer pool: 16.0K/8.0M
  
  -------- Recommendations -----------------------------------------------------
  General recommendations:
      Run OPTIMIZE TABLE to defragment tables for better performance
      MySQL started within last 24 hours - recommendations may be inaccurate
      Reduce your overall MySQL memory footprint for system stability
      Increase table_cache gradually to avoid file descriptor limits
  Variables to adjust:
    *** MySQL's maximum memory usage is dangerously high ***
    *** Add RAM before increasing MySQL buffer variables ***
      query_cache_limit (&gt; 2M, or use smaller result sets)
      table_cache (&gt; 128)
  </code></pre>
  
  <p>When I decrease <code>query_cache_limit</code> and <code>table_cache</code> it doesn't appear to have any effect.  I have restarted MySQL within the last 24 hours which could be part of the issue.</p>
  
  <p><strong>update</strong></p>
  
  <p>After running <code>SHOW STATUS LIKE  '%cache%'</code> the output is</p>
  
  <pre><code>Variable_name   Value
  Binlog_cache_disk_use   0
  Binlog_cache_use    0
  Com_assign_to_keycache  0
  Qcache_free_blocks  436
  Qcache_free_memory  23551488
  Qcache_hits 72553
  Qcache_inserts  26954
  Qcache_lowmem_prunes    0
  Qcache_not_cached   7164
  Qcache_queries_in_cache 5877
  Qcache_total_blocks 12347
  Ssl_callback_cache_hits 0
  Ssl_session_cache_hits  0
  Ssl_session_cache_misses    0
  Ssl_session_cache_mode  NONE
  Ssl_session_cache_overflows 0
  Ssl_session_cache_size  0
  Ssl_session_cache_timeouts  0
  Ssl_used_session_cache_entries  0
  Threads_cached  3
  </code></pre>