mysql监测工具tuning-primer.sh_MySQL

mysql监测工具tuning-primer.sh

mysql的运算情况，我们可以一步一步的去查看，分析。这样需要一定的时间，今天推荐给大家一个不错的脚本tuning-primer.sh，可以帮助我们去查看一下msyql的运行情况，产生报告和给出一些建议，我们可以根据这些建议，结合mysql服务器的实际情况，对mysql进行优化。

下载地址： wget http://www.day32.com/MySQL/tuning-primer.sh

注意：将tuning-primer.sh放到于my.cnf同一目录上,并赋于可读写权限

若出现如下错误

[html] [root@mwtec-80 data]# sh tuning-primer.sh   which: no bc in (/usr/local/cmake/bin:/usr/local/mysql3306/bin:/home/hadoop/hadoop-0.20.2/bin:/usr/java/jdk1.6.0_26/bin:/usr/java/jdk1.6.0_26/jre/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/home/hadoop/pig-0.10.1/bin:/root/bin)  Error: Command line calculator 'bc' not found!  

安装下 bc即可：

yum install bc [root@mwtec-80 mysql3306]# ./tuning-primer.sh         -- MYSQL PERFORMANCE TUNING PRIMER --             - By: Matthew Montgomery -MySQL Version 5.6.12 x86_64Uptime = 0 days 1 hrs 55 min 50 secAvg. qps = 0Total Questions = 50Threads Connected = 1Warning: Server has not been running for at least 48hrs.It may not be safe to use these recommendationsTo find out more information on how each of theseruntime variables effects performance visit:http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.htmlVisit http://www.mysql.com/products/enterprise/advisors.htmlfor info about MySQL's Enterprise Monitoring and Advisory ServiceSLOW QUERIES  --慢查询情况The slow query log is NOT enabled.Current long_query_time = 10.000000 sec.You have 0 out of 71 that take longer than 10.000000 sec. to completeYour long_query_time seems to be fineBINARY UPDATE LOGThe binary update log is NOT enabled.You will not be able to do point in time recoverySee http://dev.mysql.com/doc/refman/5.6/en/point-in-time-recovery.htmlWORKER THREADS  --工作线程Current thread_cache_size = 9Current threads_cached = 0Current threads_per_sec = 0Historic threads_per_sec = 0Your thread_cache_size is fineMAX CONNECTIONS  --最大连接数Current max_connections = 151Current threads_connected = 1Historic max_used_connections = 1The number of used connections is 0% of the configured maximum.You are using less than 10% of your configured max_connections.Lowering max_connections could help to avoid an over-allocation of memorySee "MEMORY USAGE" section to make sure you are not over-allocatingINNODB STATUS Current InnoDB index space = 0 bytesCurrent InnoDB data space = 80 KCurrent InnoDB buffer pool free = 97 %Current innodb_buffer_pool_size = 128 MDepending on how much space your innodb indexes take up it may be safeto increase this value to up to 2 / 3 of total system memoryMEMORY USAGE  --内存使用情况Max Memory Ever Allocated : 154 MConfigured Max Per-thread Buffers : 169 MConfigured Max Global Buffers : 153 MConfigured Max Memory Limit : 322 MPhysical Memory : 3.61 GMax memory limit seem to be within acceptable normsKEY BUFFER  --myisam表的key buffer使用情况No key reads?!Seriously look into using some indexesCurrent MyISAM index space = 108 KCurrent key_buffer_size = 8 MKey cache miss rate is 1 : 0Key buffer free ratio = 81 %Your key_buffer_size seems to be fineQUERY CACHE --查询缓存情况Query cache is enabledCurrent query_cache_size = 1 MCurrent query_cache_used = 16 KCurrent query_cache_limit = 1 MCurrent Query cache Memory fill ratio = 1.64 %Current query_cache_min_res_unit = 4 KYour query_cache_size seems to be too high.Perhaps you can use these resources elsewhereMySQL won't cache query results that are larger than query_cache_limit in sizeSORT OPERATIONS --排序情况Current sort_buffer_size = 256 KCurrent read_rnd_buffer_size = 256 KNo sort operations have been performedSort buffer seems to be fineJOINS  --连接情况Current join_buffer_size = 260.00 KYou have had 0 queries where a join could not use an index properlyYour joins seem to be using indexes properlyOPEN FILES LIMIT  --最大打开文件情况Current open_files_limit = 5000 filesThe open_files_limit should typically be set to at least 2x-3xthat of table_cache if you have heavy MyISAM usage.Your open_files_limit value seems to be fineTABLE CACHE --表缓存使用情况Current table_open_cache = 2000 tablesCurrent table_definition_cache = 1400 tablesYou have a total of 80 tablesYou have 80 open tables.The table_cache value seems to be fineTEMP TABLES -- 临时表使用情况Current max_heap_table_size = 16 MCurrent tmp_table_size = 16 MOf 337 temp tables, 10% were created on diskCreated disk tmp tables ratio seems fineTABLE SCANS -- 表扫描情况Current read_buffer_size = 128 KCurrent table scan ratio = 6 : 1read_buffer_size seems to be fineTABLE LOCKING --表锁定情况Current Lock Wait ratio = 0 : 326Your table locking seems to be fine