mysql - 查询缓存效率

Question

我正在使用 MySQLTuner.pl 来优化我的网站....虽然我不完全确定如何解决其中的一些问题并且想知道是否有人可以帮助我。

我使用以下 MySQL 设置运行 16GB 内存:

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，这可能是问题的一部分。

更新

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

Answer 1

我发现这个网站有助于优化我自己的 mysql 服务器:http://www.omh.cc/mycnf/

它允许您调整变量并了解内存的总容量。您需要针对 60% 的 ram 使用率进行优化。因此，请尝试将总内存占用量降低到总内存的 60% 到 70% 左右。如果您在同一台机器上运行其他东西，您可能需要减少该数量。忘掉 Query cache，它不会增加太多值(value)，但如果处理得当，Table cache 应该会提高您的性能。

尝试减少连接数并将总内存占用量控制在总系统内存的 60% 以下。