MySQL Tune

最新推荐文章于 2024-09-02 09:22:15 发布

jerry-89

最新推荐文章于 2024-09-02 09:22:15 发布

阅读量301

点赞数

分类专栏： SQL优化

本文链接：https://blog.csdn.net/eagle89/article/details/105297307

版权

SQL优化专栏收录该内容

23 篇文章 6 订阅

订阅专栏

MySQL Tuner是一个Perl脚本，它连接到正在运行的MySQL实例，并根据工作负载提供配置建议。理想情况下，MySQL实例应该在运行脚本之前至少运行24小时。实例运行的时间越长，MySQL Tuner给出的建议就越好。

github网址如下：

https://github.com/major/MySQLTuner-perl

代码提交还是比较活跃的，目前有4.3k个star

由于这个软件是用perl写的，所有要安装perl

yum install perl wget -y

将MySQL Tuner下载到您的主目录。

wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl

要运行它：

perl ./mysqltuner.pl

系统将要求您提供MySQL root用户的名称和密码。

执行结果如下：

perl ./mysqltuner.pl
>> MySQLTuner 1.7.19 - Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering

[--] Skipped version check for MySQLTuner script
Please enter your MySQL administrative login:
Please enter your MySQL administrative password: [!!] failed to execute: SHOW SLAVE STATUS\G
[!!] FAIL Execute SQL / return code: 256
[!!] failed to execute: SHOW SLAVE HOSTS
[!!] FAIL Execute SQL / return code: 256
[!!] Your MySQL version 5.5.56-log is EOL software! Upgrade soon!
[OK] Operating on 64-bit architecture

针对给出的意见进行修改
-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Restrict Host for user@% to user@SpecificDNSorIp
    MySQL started within last 24 hours - recommendations may be inaccurate
    Reduce your overall MySQL memory footprint for system stability
    Enable the slow query log to troubleshoot bad queries
    Reduce or eliminate unclosed connections and network issues
    Increasing the query_cache size over 128M may reduce performance
    Upgrade to MySQL 5.5+ to use asynchrone write
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***

query_cache_size (> 128M) [see warning above]

注意：

浏览输出的结果，特别是末尾的 Recommendations ，里面一般会提到您需要在 my.cnf 修改的内容。修改 my.cnf 后记得重启 MySQL 。重启后再运行 MySQLTuner 检查。另外需要注意的是 MySQL 需要启动 24 小时候再运行 MySQLTuner ，不然有些内容会不准。