有任何上古卷轴的问题,请直接到MOD社区发帖求助

借助 MySQLTuner 优化 MySQL 性能

MySQLTuner 是一个 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 ,不然有些内容会不准。

icedream

About icedream

其实我知道的东西很少,只是翻译了一些东西,悲催的是翻译过后很快就忘了。

Comments are closed.