霜天部落 | 专注PHP研发,研究LAMP高性能架构部署与优化

MySQL 优化工具 MySQLTuner

MySQLTuner是一个方便的MySQL优化工具,MySQLTuner是一个Perl脚本,用来分析你的MySQL性能,并给出优化建议。

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)

原文地址: http://www.21andy.com/blog/20100414/1864.html