Mysql性能检测优化脚本mysqltuner.pl

MySQLTuner是一个Perl编写的脚本，可以帮助你检测mysql，从而快速准确的进行性能调优，以提高性能和稳定性，通过这个脚本可以到mysql的配置和数据状态，还有一些优化建议！需要的朋友可以玩玩看！

官方站点：

• https://github.com/rackerhacker/MySQLTuner-perl
• http://mysqltuner.com/

下载

wget http://mysqltuner.pl/mysqltuner.pl

[root@sunk ~]# perl mysqltuner.pl --socket=/tmp/mysql.sock

 >>  MySQLTuner 1.2.0 - 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:

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.66
[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: 527K (Tables: 51)
[!!] InnoDB is enabled but isn't being used
[!!] Total fragmented tables: 16

-------- Security Recommendations  -------------------------------------------
[!!] User '@localhost' has no password set.
[!!] User '@sunk' has no password set.
[!!] User 'root@127.0.0.1' has no password set.
[!!] User 'root@sunk' has no password set.

-------- Performance Metrics -------------------------------------------------
[--] Up for: 12d 2h 19m 5s (150K q [0.144 qps], 4K conn, TX: 190M, RX: 19M)
[--] Reads / Writes: 88% / 12%
[--] Total buffers: 23.0M global + 6.9M per thread (100 max threads)
[!!] Maximum possible memory usage: 716.8M (287% of installed RAM)
[OK] Slow queries: 0% (0/150K)
[OK] Highest usage of available connections: 6% (6/100)
[OK] Key buffer size / total MyISAM indexes: 8.0M/275.0K
[OK] Key buffer hit rate: 99.9% (82K cached / 95 reads)
[OK] Query cache efficiency: 87.6% (102K cached / 116K selects)
[OK] Query cache prunes per day: 27
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 1K sorts)
[!!] Temporary tables created on disk: 49% (13K on disk / 28K total)
[OK] Thread cache hit rate: 99% (6 created / 4K connections)
[OK] Table cache hit rate: 33% (65 open / 195 opened)
[OK] Open file limit used: 8% (125/1K)
[OK] Table locks acquired immediately: 99% (13K immediate / 13K locks)

-------- Recommendations -----------------------------------------------------
General recommendations:
    Add skip-innodb to MySQL configuration to disable InnoDB
    Run OPTIMIZE TABLE to defragment tables for better performance
    Reduce your overall MySQL memory footprint for system stability
    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
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    tmp_table_size (> 4M)
    max_heap_table_size (> 32M)