查询缓存效率 [英] Query cache efficiency
问题描述
我正在使用MySQLTuner.pl来优化我的网站....尽管我不确定如何解决其中一些问题，并且想知道是否有人可以帮助我.
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.
我正在使用以下MySQL设置运行16GB的RAM:
I'm running 16GB of RAM with the following MySQL settings:
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
这是我的调谐器的输出
-------- 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)
当我减小query_cache_limit和table_cache时，它似乎没有任何作用.我在过去24小时内重新启动了MySQL，这可能是问题的一部分.
When I decrease query_cache_limit and table_cache it doesn't appear to have any effect. I have restarted MySQL within the last 24 hours which could be part of the issue.
更新
运行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
推荐答案
I found this website helpful in optimizing my own mysql server: http://www.omh.cc/mycnf/
它允许您调整变量并知道总的柱塞容量是多少.您需要针对60％的ram使用进行优化.因此，请尝试将总内存占用降低到总内存的60％到70％.如果您在同一台计算机上运行其他内容，则可能需要减少该数量.忘了Query cache，它不会增加太多价值，但是Table cache如果做得正确，应该会提高您的性能.
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 cache should improve your performance if done right.
请尝试减少连接数量，并将总内存占用保持在系统总内存的60％以下.
Try reducing the number of connections and keeping your total memory footprint under 60% of total system memory.
这篇关于查询缓存效率的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
