我有一个运行 Zen Cart 的 VPS。
该服务器基于 CentOS 5.7 64Bit 构建，具有 2 个虚拟 CPU 和 2GB RAM。我尝试使用 mysqltuner 脚本来优化我的 mysql 数据库。
当前 query_cache_size 设置为 120MB，接近 128MB 的限制。我不确定是否应该按照建议继续增加这个数字。
另外，您能告诉我为什么每天的查询缓存修剪次数（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
答案1
query_cache_size 不限于 128MB。但值过高可能会降低性能。
由于碎片化，您可能会有大量查询缓存修剪，而不一定是由于内存不足。
您应该能够安全地将查询缓存大小增加到 160MB 甚至 192MB 左右，但如果您开始看到性能下降，则可能需要将其调低。
答案2
如果您的 MySQL 表一直在变化，查询缓存就无法像处理（大多数）只读表那样高效。如果数据发生变化，查询缓存必须至少使该条目无效并重新读取它。