MySQLTuner 是一个 Perl 脚本,可以用来分析您的 MySQL 性能,并且基于收集到的信息给出相应的优化建议。这样子,您就可以调整 my.cnf 从而优化您的 MySQL 设置。
这边只是介绍使用方法,不保证说 MySQLTuner 对您就是绝对有效。
首先需要下载 MySQLTuner
# wget https://raw.github.com/major/MySQLTuner-perl/master/mysqltuner.pl
设置权限为可执行
# chmod +x mysqltuner.pl
然后就可以直接运行了
# ./mysqltuner.pl
如果遇到错误 Unable to find mysqladmin in your $PATH 可以输入
# PATH=$PATH:/usr/local/mysql/bin
PS:把 /usr/local/mysql/bin 改为您 MySQL 程序 mysqladmin 的路径
运行后的内容如下:
1 >> MySQLTuner 1.2.0 -MySQL High Performance Tuning Script2 >> Bug reports, feature requests, and downloads at http://mysqltuner.com/
3 >> Run with '--help' foradditional options and output filtering4 Please enter your MySQL administrative login:
7 -------- General Statistics --------------------------------------------------
8 [--] Skipped version check forMySQLTuner script9 [!!] Your MySQL version 4.1.11-Debian_etch1-log is EOL software! Upgrade soon!
10 [OK] Operating on 32-bit architecture with lessthan 2GB RAM11
12 -------- Storage Engine Statistics -------------------------------------------
13 [--] Status: +Archive -BDB -Federated +InnoDB +ISAM -NDBCluster14 [--] Data in MyISAM tables: 301M (Tables: 2074)15 [--] Data in HEAP tables: 379K (Tables: 9)16 [!!] InnoDB is enabled but isn't being used
17 [!!] ISAM is enabled but isn't being used
18 [!!] Total fragmented tables: 215
19
20 -------- Performance Metrics -------------------------------------------------
21 [--] Up for: 12d 18h 33m 30s (1B q [1K qps], 185K conn, TX: 3B, RX: 377M)22 [--] Reads / Writes: 78% / 22%
23 [--] Total buffers: 2.6M per thread and 58.0Mglobal24 [OK] Maximum possible memory usage: 320.5M (20%of installed RAM)25 [OK] Slow queries: 0% (17/1B)26 [OK] Highest usage of available connections: 32% (32/100)27 [OK] Key buffer size / total MyISAM indexes: 16.0M/72.3M
28 [OK] Key buffer hit rate: 99.9%
29 [OK] Query cache efficiency: 99.9%
30 [!!] Query cache prunes per day: 47549
31 [OK] Sorts requiring temporary tables: 0%
32 [!!] Temporary tables created on disk: 28%
33 [OK] Thread cache hit rate: 99%
34 [!!] Table cache hit rate: 0%
35 [OK] Open file limit used: 12%
36 [OK] Table locks acquired immediately: 99%
37 [!!] Connections aborted: 20%
38
39 -------- Recommendations -----------------------------------------------------
40 General recommendations:41 Add skip-innodb to MySQL configuration to disable InnoDB42 Add skip-isam to MySQL configuration to disable ISAM43 Run OPTIMIZE TABLE to defragment tables forbetter performance44 Enable the slow query log to troubleshoot bad queries45 When making adjustments, make tmp_table_size/max_heap_table_size equal46 Reduce your SELECT DISTINCT queries without LIMIT clauses47 Increase table_cache gradually to avoid filedescriptor limits48 Your applications are not closing MySQL connections properly49 Variables to adjust:50 query_cache_size (>16M)51 tmp_table_size (>32M)52 max_heap_table_size (>16M)53 table_cache (> 64)
浏览输出的结果,特别是末尾的 Recommendations ,里面一般会提到您需要在 my.cnf 修改的内容。修改 my.cnf 后记得重启 MySQL 。重启后再运行 MySQLTuner 检查。另外需要注意的是 MySQL 需要启动 24 小时候再运行 MySQLTuner ,不然有些内容会不准。