mysql参数优化辅助工具之mysqltuner-SQLServer MySQL-51CTO博客

remington_young

　　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
　　>>  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>　　[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>　　[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 (=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:变量调整建议
　　参考资料:
　　http://blog.chinaunix.net/uid-16844903-id-351026.html