MySQL 查询缓存效率
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4139936/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Query cache efficiency
提问by Webnet
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.
我正在使用 MySQLTuner.pl 来优化我的网站......虽然我不完全确定如何解决其中一些问题，并且想知道是否有人可以帮助我。
I'm running 16GB of RAM with the following MySQL settings:
我正在使用以下 MySQL 设置运行 16GB 的 RAM：
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
Here's the output of my tuner
这是我的调谐器的输出
-------- 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 (> 2M, or use smaller result sets)
table_cache (> 128)
When I decrease query_cache_limitand table_cacheit doesn't appear to have any effect. I have restarted MySQL within the last 24 hours which could be part of the issue.
当我减少query_cache_limit并且table_cache它似乎没有任何影响时。我在过去 24 小时内重新启动了 MySQL，这可能是问题的一部分。
update
更新
After running SHOW STATUS LIKE '%cache%'the output is
运行后SHOW STATUS LIKE '%cache%'输出是
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
采纳答案by Mohamed Nuur
I found this website helpful in optimizing my own mysql server: http://www.omh.cc/mycnf/
我发现这个网站有助于优化我自己的 mysql 服务器：http: //www.omh.cc/mycnf/
It allows you to tune your variables and know what the total ram capacity will be. You need to optimize for 60% ram usage. So try to lower your total memory footprint to about 60% to 70% of your total ram. If you have other stuff running on the same machine, you probably need to reduce that number. Forget Query cache, it's not going to add too much value, but Table cacheshould improve your performance if done right.
它允许您调整变量并知道总内存容量是多少。您需要针对 60% 的内存使用进行优化。因此，请尝试将总内存占用量降低到总内存的 60% 到 70% 左右。如果您在同一台机器上运行其他东西，您可能需要减少该数量。忘记吧Query cache，它不会增加太多的价值，但Table cache如果做得对，应该会提高你的表现。
Try reducing the number of connections and keeping your total memory footprint under 60% of total system memory.
尝试减少连接数并将总内存占用量保持在系统总内存的 60% 以下。
