mysql tuner_借助 MySQLTuner 优化 MySQL 性能（转载的一篇文章）

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 ，不然有些内容会不准。