mysql参数优化辅助工具之mysqltuner-SQLServer MySQL-51CTO博客
Mysqltuner.sql 的使用和安装。Mysqltuner 是一个mysql参数优化辅助工具,它可以对mysql进行全面的体检,然后给出针对性的体检报告
1.mysqltuner 下载
mysqltuner 官网：http://mysqltuner.com/ 可以下载最新版本的。
2.mysqltuner 的安装和使用。
Mysqltuner就是一套perl脚本,不需要任何的安装，将下载下来的tar安装包解压下就行了。
# tar -zxvf major-MySQLTuner-perl-v1.4.0-4-gfa47d9c.tar.gz
# cd major-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
Logged in using credentials passed on the command line
Currently running supported MySQL version 5.5.17-log
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 -------------------------------------------
All database users have passwords assigned
-------- Performance Metrics-------------------------------------------------
[--] Up for: 11d 22h 38m 29s (11M q , 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)
Slow queries: 0% (41K/11M)
[!!] Highest connection usage: 99% (1996/2000)
Key buffer> Key buffer hit rate: 100.0% (1M cached / 8 reads)
[!!] Query cache is disabled
Sorts requiring temporary tables: 0% (0 temp sorts / 649 sorts)
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)
Open file limit used: 0% (57/65K)
Table locks acquired immediately: 97% (22M immediate / 22Mlocks)
[!!] InnoDBbuffer pool /data> 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
页:
[1]