Mysql优化工具－－MySQLTuner

最近发现一个优化Mysql时可以使用的小工具－－MySQLTuner，这个工具是一个prel写的脚本，运行后会给出当前mysql的各种参数，对进一步优化起到很好的指示。

官方网站：http://rackerhacker.com/

官方wiki：http://wiki.mysqltuner.com/MySQLTuner

下载地址：http://mysqltuner.com/mysqltuner.pl

SVN: svn co http://tools.assembla.com/svn/mysqltuner/

运行的结果：

server1:~# ./mysqltuner.pl



>> MySQLTuner 0.9.8 - Major Hayden <major@mhtx.net>

>> 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)