mysql性能优化脚本mysqltuner.pl使用介绍

mysqltuner 下载地址：http://github.com/rackerhacker/mysqltuner-perl

mysqltuner 使用方法：
downloading and using mysqltuner is actually a very simple process:

wget mysqltuner.pl
perl mysqltuner.pl

if you'd rather not invoke perl every time, just make it executable:

chmod u+x mysqltuner.pl
./mysqltuner.pl

» latest development version
if you want to get the bleeding edge commits, you can check out the latest code with git:

git clone git://github.com/rackerhacker/mysqltuner-perl.git

看看我的运行结果

>> mysqltuner 1.1.1 - major hayden
>> bug reports, feature requests, and downloads at http://mysqltuner.com/
>> run with '--help' for additional options and output filtering

-------- general statistics --------------------------------------------------
[--] skipped version check for mysqltuner script
[ok] currently running supported mysql version 5.1.43-log
[ok] operating on 64-bit architecture

-------- storage engine statistics -------------------------------------------
[--] status: -archive -bdb -federated -innodb -isam -ndbcluster
[--] data in myisam tables: 889m (tables: 479)
[--] data in memory tables: 1m (tables: 4)
[!!] total fragmented tables: 29

-------- security recommendations -------------------------------------------
[ok] all database users have passwords assigned

-------- performance metrics -------------------------------------------------
[--] up for: 12h 55m 27s (4m q [105.230 qps], 295k conn, tx: 9b, rx: 546m)
[--] reads / writes: 82% / 18%
[--] total buffers: 432.0m global + 6.3m per thread (500 max threads)
[!!] maximum possible memory usage: 3.5g (175% of installed ram)
[ok] slow queries: 0% (48k/4m)
[ok] highest usage of available connections: 3% (16/500)
[ok] key buffer size / total myisam indexes: 384.0m/332.6m
[ok] key buffer hit rate: 100.0% (2b cached / 205k reads)
[ok] query cache efficiency: 49.3% (1m cached / 3m selects)
[!!] query cache prunes per day: 268042
[ok] sorts requiring temporary tables: 0% (10 temp sorts / 122k sorts)
[ok] temporary tables created on disk: 19% (4k on disk / 22k total)
[ok] thread cache hit rate: 99% (16 created / 295k connections)
[!!] table cache hit rate: 18% (256 open / 1k opened)
[ok] open file limit used: 19% (497/2k)
[!!] table locks acquired immediately: 88%

-------- recommendations -----------------------------------------------------
general recommendations:
run optimize table to defragment tables for better performance
mysql started within last 24 hours - recommendations may be inaccurate
reduce your overall mysql memory footprint for system stability
increase table_cache gradually to avoid file descriptor limits
optimize queries and/or use innodb to reduce lock wait
variables to adjust:
*** mysql's maximum memory usage is dangerously high ***
*** add ram before increasing mysql buffer variables ***
query_cache_size (> 32m)
table_cache (> 256)