mysqltuner suggestion

Question

I have a VPS running Zen Cart.

The server is built on CentOS 5.7 64Bit, and has 2 Virtual CPUs and 2GB RAM. I try to use the mysqltuner script to optimize my mysql database.

The current query_cache_size is set to 120MB which is close to the 128MB limit. I am not sure I should continue increase this number as suggested or not.

Also, could you tell me why the Query cache prunes per day(171858) is so high? How can I to reduce this?

 -------- Performance Metrics -------------------------------------------------
 [--] Up for: 7h 26m 48s (2M q [83.497 qps], 17K conn, TX: 6B, RX: 377M)
 [--] Reads / Writes: 87% / 13%
 [--] Total buffers: 196.0M global + 3.1M per thread (100 max threads)
 [OK] Maximum possible memory usage: 507.3M (24% of installed RAM)
 [OK] Slow queries: 0% (2/2M)
 [OK] Highest usage of available connections: 8% (8/100)
 [OK] Key buffer size / total MyISAM indexes: 50.0M/27.8M
 [OK] Key buffer hit rate: 100.0% (6M cached / 1K reads)
 [OK] Query cache efficiency: 88.4% (1M cached / 2M selects)
 [!!] Query cache prunes per day: 171858
 [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 13K sorts)
 [!!] Joins performed without indexes: 269
 [OK] Temporary tables created on disk: 12% (1K on disk / 15K total)
 [OK] Thread cache hit rate: 99% (97 created / 17K connections)
 [OK] Table cache hit rate: 94% (434 open / 460 opened)
 [OK] Open file limit used: 16% (685/4K)
 [OK] Table locks acquired immediately: 99% (396K immediate / 396K locks)

 -------- Recommendations -----------------------------------------------------
 General recommendations:
  Run OPTIMIZE TABLE to defragment tables for better performance
  MySQL started within last 24 hours - recommendations may be inaccurate
  Enable the slow query log to troubleshoot bad queries
  Adjust your join queries to always utilize indexes
 Variables to adjust:
  query_cache_size (> 120M)
  join_buffer_size (> 500.0K, or always use indexes with joins)



 ###My current my.cnf settings###
 query_cache_size = 120M
 thread_cache_size = 4
 table_cache = 1000
 key_buffer_size = 50M
 query_cache_limit = 20M
 join_buffer_size = 500K

Answer 1

The query_cache_size isn't limited to 128MB. Having too high a value for it can degrade performance though.

You can have a lot of query cache prunes due to fragmentation, not necessarily low memory.

You should be able to safely increase your query cache size to something like 160MB or even 192MB, but if you start seeing degradation, you may want to tone it down.

Answer 2

If your MySQL tables are changing all the time, query cache cannot be as efficient as it would be with (mostly) read-only tables. If the data changes, query cache must invalidate at least that entry and re-read it.