用于性能优化的 mysqltuner 报告

Question

MySQL 优化技术的新手。请找到下面的 mysqltuner.pl 报告，并建议我应该更改 my.cnf 中的哪些变量以优化性能。

还有一个问题-

我无法在我的 my.cnf 中找到一些变量，例如 query_cache_size、thread_cache_size。我可以明确地将它们添加到 my.cnf 吗？

报告:

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.8
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 10K (Tables: 10)
[--] Data in InnoDB tables: 176M (Tables: 60)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 61

-------- Performance Metrics -------------------------------------------------
[--] Up for: 21h 15m 41s (237K q [3.099 qps], 42 conn, TX: 29M, RX: 52M)
[--] Reads / Writes: 68% / 32%
[--] Total buffers: 184.0M global + 1.6M per thread (151 max threads)
[OK] Maximum possible memory usage: 419.9M (20% of installed RAM)
[OK] Slow queries: 0% (0/237K)
[OK] Highest usage of available connections: 3% (5/151)
[OK] Key buffer size / total MyISAM indexes: 16.0M/2.3M
[OK] Key buffer hit rate: 100.0% (610K cached / 7 reads)
[!!] Query cache is disabled
[!!] Sorts requiring temporary tables: 38% (5 temp sorts / 13 sorts)
[OK] Temporary tables created on disk: 0% (6 on disk / 67K total)
[!!] Thread cache is disabled
[OK] Table cache hit rate: 83% (36 open / 43 opened)
[OK] Open file limit used: 0% (18/65K)
[OK] Table locks acquired immediately: 100% (257K immediate / 257K locks)
[!!] InnoDB data size / buffer pool: 176.3M/128.0M

-------- 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
    Set thread_cache_size to 4 as a starting value
Variables to adjust:
    query_cache_size (>= 8M)
    sort_buffer_size (> 512K)
    read_rnd_buffer_size (> 512K)
    thread_cache_size (start at 4)
    innodb_buffer_pool_size (>= 176M)

Answer 1

是的。您可以将变量添加到 my.cnf并且，根据 MySQL 的版本，动态更改它们 - 在 mysql 控制台中。