mysql参数优化辅助工具之mysqltuner

Mysqltuner.sql 的使用和安装。

Mysqltuner 是一个mysql参数优化辅助工具,它可以对mysql进行全面的体检,然后给出针对性的体检报告

1.mysqltuner 下载

mysqltuner 官网：http://mysqltuner.com/ 可以下载最新版本的。

2.mysqltuner 的安装和使用。

Mysqltuner  就是一套perl脚本,不需要任何的安装，将下载下来的tar安装包解压下就行了。

[root@DG-DB software]# tar -zxvf major-MySQLTuner-perl-v1.4.0-4-gfa47d9c.tar.gz

[root@DG-DB software]# cd major-MySQLTuner-perl-fa47d9c

[root@DG-DBmajor-MySQLTuner-perl-fa47d9c]# ./mysqltuner.pl --user root  --pass anyu@2015

 >>  MySQLTuner 1.4.0 - Major Hayden<[email protected]>

 >>  Bug reports, feature requests, and downloadsat http://mysqltuner.com/

 >>  Run with ‘--help‘ for additional options andoutput filtering

[OK] Logged in using credentials passed on the command line

[OK] Currently running supported MySQL version 5.5.17-log

[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------

[--] Status: +CSV +InnoDB +MRG_MYISAM

[--] Data in InnoDB tables: 32G (Tables: 15)

[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)

[!!] Total fragmented tables: 1

-------- Security Recommendations -------------------------------------------

[OK] All database users have passwords assigned

-------- Performance Metrics-------------------------------------------------

[--] Up for: 11d 22h 38m 29s (11M q [11.157 qps], 5K conn, TX: 95B,RX: 29B)

[--] Reads / Writes: 45% / 55%

[--] Total buffers: 4.3G global + 2.1M per thread (2000 max threads)

[!!] Maximum possible memory usage: 8.5G (110% of installed RAM)

[OK] Slow queries: 0% (41K/11M)

[!!] Highest connection usage: 99% (1996/2000)

[OK] Key buffer size / total MyISAM indexes: 256.0M/96.0K

[OK] Key buffer hit rate: 100.0% (1M cached / 8 reads)

[!!] Query cache is disabled

[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 649 sorts)

[OK] Temporary tables created on disk: 0% (134 on disk / 133K total)

[!!] Thread cache hit rate: 24% (4K created / 5K connections)

[!!] Table cache hit rate: 0% (59 open / 7K opened)

[OK] Open file limit used: 0% (57/65K)

[OK] Table locks acquired immediately: 97% (22M immediate / 22Mlocks)

[!!] InnoDB  buffer pool /data size: 4.0G/32.0G

[OK] InnoDB log waits: 0

-------- Recommendations-----------------------------------------------------

General recommendations:

    Run OPTIMIZE TABLE todefragment tables for better performance

    Reduce your overall MySQLmemory footprint for system stability

    Reduce or eliminatepersistent connections to reduce connection usage

    Increase table_open_cachegradually to avoid file descriptor limits

    Read this beforeincreasing table_open_cache over 64: http://bit.ly/1mi7c4C

Variables to adjust:

  *** MySQL‘s maximum memoryusage is dangerously high ***

  *** Add RAM beforeincreasing MySQL buffer variables ***

    max_connections (>2000)

    wait_timeout (< 200)

    interactive_timeout (<200)

    query_cache_size (>=8M)

    thread_cache_size (>64)

    table_open_cache (>4220)

innodb_buffer_pool_size (>= 32G)

从mysqltuner的输出可以看出mysqltuner的输出主要包含以下几大块

Storage Engine Statistics:存储引擎的相关统计

Security Recommendations：安装检查

Performance Metrics：性能指标

General recommendations: 物理规划建议

Variables to adjust:变量调整建议

本文出自 “SQLServer MySQL” 博客，请务必保留此出处http://dwchaoyue.blog.51cto.com/2826417/1636616