title: MySQL 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 have about 4GB ram free if needed for MySQL with the following MySQL settings:</p>
  
  <pre><code>key_buffer      = 100M
  max_allowed_packet  = 16M
  thread_stack        = 192K
  thread_cache_size       = 800
  
  myisam-recover         = BACKUP
  max_connections        = 750
  table_cache            = 125000
  thread_concurrency     = 500
  
  query_cache_type=1
  query_cache_limit = 128M
  query_cache_size        = 128M
  
  tmp_table_size = 300M
  max_heap_table_size = 300M
  innodb_buffer_pool_size = 2G
  innodb_file_per_table = 0
  innodb_flush_log_at_trx_commit = 2
  </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.5.24-0ubuntu0.12.04.1
  [OK] Operating on 64-bit architecture
  
  -------- Storage Engine Statistics -------------------------------------------
  [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
  [--] Data in MyISAM tables: 7K (Tables: 10)
  [--] Data in InnoDB tables: 27M (Tables: 5)
  [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
  [!!] Total fragmented tables: 5
  
  -------- Security Recommendations  -------------------------------------------
  [OK] All database users have passwords assigned
  
  -------- Performance Metrics -------------------------------------------------
  [--] Up for: 15d 5h 4m 19s (1B q [1K qps], 208M conn, TX: 172B, RX: 98B)
  [--] Reads / Writes: 71% / 29%
  [--] Total buffers: 2.5G global + 2.7M per thread (750 max threads)
  [OK] Maximum possible memory usage: 4.5G (57% of installed RAM)
  [OK] Slow queries: 0% (0/1B)
  [OK] Highest usage of available connections: 15% (118/750)
  [OK] Key buffer size / total MyISAM indexes: 100.0M/119.0K
  [OK] Key buffer hit rate: 100.0% (22M cached / 0 reads)
  [!!] Query cache efficiency: 0.1% (703K cached / 519M selects)
  [OK] Query cache prunes per day: 0
  [OK] Sorts requiring temporary tables: 0% (13 temp sorts / 13M sorts)
  [OK] Temporary tables created on disk: 25% (4M on disk / 18M total)
  [OK] Thread cache hit rate: 99% (752 created / 208M connections)
  [OK] Table cache hit rate: 74% (992 open / 1K opened)
  [OK] Open file limit used: 0% (68/250K)
  [OK] Table locks acquired immediately: 100% (216M immediate / 216M locks)
  [OK] InnoDB data size / buffer pool: 27.8M/2.0G
  
  -------- Recommendations -----------------------------------------------------
  General recommendations:
  Run OPTIMIZE TABLE to defragment tables for better performance
  Enable the slow query log to troubleshoot bad queries
  Variables to adjust:
  query_cache_limit (&gt; 128M, or use smaller result sets)
  </code></pre>
  
  <p>The output of <code>SHOW STATUS LIKE '%cache%'</code> is</p>
  
  <pre><code>Binlog_cache_disk_use   0
  Binlog_cache_use    0
  Binlog_stmt_cache_disk_use  0
  Binlog_stmt_cache_use   0
  Com_assign_to_keycache  0
  Qcache_free_blocks  19
  Qcache_free_memory  134026728
  Qcache_hits 704192
  Qcache_inserts  143569852
  Qcache_lowmem_prunes    0
  Qcache_not_cached   11043040
  Qcache_queries_in_cache 94
  Qcache_total_blocks 217
  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  748
  </code></pre>
  
  <p>Is there anything I also can improve for better performance?</p>
  
  <p>Thank you</p>