mysqltuner建议

Question

我有一辆开禅车的VPS。

服务器构建在CentOS 5.7 64位上，有2个虚拟CPU和2GBRAM。我尝试使用mysqltuner脚本来优化mysql数据库。

当前的query_cache_size设置为120 is，接近128 is的限制。我不确定是否应继续按建议增加这一数字。

另外，你能告诉我为什么每天(171858)的查询缓存修剪如此之高吗？我怎样才能减少这种情况？

 -------- 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

query_cache_size不限于128 to。但是，过高的值会降低性能。

您可以有许多查询缓存剪枝，因为碎片，不一定是低内存。

您应该能够安全地将查询缓存大小增加到类似于160 it甚至192 it的大小，但是如果您开始看到下降，您可能希望将其降下来。

Answer 2

如果您的MySQL表一直在变化，查询缓存就不能像(大部分)只读表那样高效。如果数据发生变化，查询缓存必须使该条目失效并重新读取.