bitscn.com
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
bitscn.com