利用MySQLTuner 优化 MySQL 性能（亲测）

MySQLTuner(https://github.com/major/MySQLTuner-perl/) 是一个 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 的路径

运行后的内容如下：

>> MySQLTuner 1.2.0 – MySQL High Performance Tuning Script
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with ‘–help’ for additional options and output filtering
Please enter your MySQL administrative login: <-- root Please enter your MySQL administrative password: <-- yourrootsqlpassword -------- General Statistics -------------------------------------------------- [--] Skipped version check for MySQLTuner script [!!] Your MySQL version 4.1.11-Debian_etch1-log is EOL software! Upgrade soon! [OK] Operating on 32-bit architecture with less than 2GB RAM -------- Storage Engine Statistics ------------------------------------------- [--] Status: +Archive -BDB -Federated +InnoDB +ISAM -NDBCluster [--] Data in MyISAM tables: 301M (Tables: 2074) [--] Data in HEAP tables: 379K (Tables: 9) [!!] InnoDB is enabled but isn't being used [!!] ISAM is enabled but isn't being used [!!] Total fragmented tables: 215 -------- Performance Metrics ------------------------------------------------- [--] Up for: 12d 18h 33m 30s (1B q [1K qps], 185K conn, TX: 3B, RX: 377M) [--] Reads / Writes: 78% / 22% [--] Total buffers: 2.6M per thread and 58.0M global [OK] Maximum possible memory usage: 320.5M (20% of installed RAM) [OK] Slow queries: 0% (17/1B) [OK] Highest usage of available connections: 32% (32/100) [OK] Key buffer size / total MyISAM indexes: 16.0M/72.3M [OK] Key buffer hit rate: 99.9% [OK] Query cache efficiency: 99.9% [!!] Query cache prunes per day: 47549 [OK] Sorts requiring temporary tables: 0% [!!] Temporary tables created on disk: 28% [OK] Thread cache hit rate: 99% [!!] Table cache hit rate: 0% [OK] Open file limit used: 12% [OK] Table locks acquired immediately: 99% [!!] Connections aborted: 20% -------- Recommendations ----------------------------------------------------- General recommendations: Add skip-innodb to MySQL configuration to disable InnoDB Add skip-isam to MySQL configuration to disable ISAM Run OPTIMIZE TABLE to defragment tables for better performance Enable the slow query log to troubleshoot bad queries When making adjustments, make tmp_table_size/max_heap_table_size equal Reduce your SELECT DISTINCT queries without LIMIT clauses Increase table_cache gradually to avoid file descriptor limits Your applications are not closing MySQL connections properly Variables to adjust: query_cache_size (> 16M)
tmp_table_size (> 32M)
max_heap_table_size (> 16M)
table_cache (> 64)
浏览输出的结果，特别是末尾的 Recommendations ，里面一般会提到您需要在 my.cnf 修改的内容。修改 my.cnf 后记得重启 MySQL 。重启后再运行 MySQLTuner 检查。另外需要注意的是 MySQL 需要启动 24 小时候再运行 MySQLTuner ，不然有些内容会不准。
来自：http://tesfans.org/tuning-mysql-performance-with-mysqltuner/

本博主亲测，本脚本对MySQL 性能优化很有效果，但是5.5.20版本以下，因bug问题请注意table_open_cache不要超过64，否则会降低性能（详见：http://bit.ly/1mi7c4C）。
官方：http://bugs.mysql.com/bug.php?id=49177