转载-凯发app官方网站

使用tuning-primer.sh 可以自动检测mysql的性能和一些建议，转载一篇文章列举一些参数意义
脚本支持my.cnf文件，可以把user, password, host, socket等连接参数放在~/.my.cnf文件里，如果使用了自定义的socket文件，请修改tuning-primer.sh文件中关于socket文件位置的变量。

运行 tuning-primer.sh显示说明

slow queries	慢查询检查
slow queriesthe slow query log is enabled.	说明我已经启用了慢查询记录功能。也就是参数 slow_query_log = 1
current long_query_time = 5.000000 sec.	慢查询的阀值时间。也就是参数 long_query_time = 5
you have 17 out of 638844 that take longer than 5.000000 sec. to complete	说明慢查询日志中记录了17条查询时间超过5秒的语句。 slow_query_log_file=/data/ats_db/mysql-slow.log设置慢查询日志路径。使用 mysqldumpslow命令查询慢日志
your long_query_time seems to be fine	慢查询阀值时间设置得在推荐的范围内

binary update log	更新二进制日志文件
the binary update log is enabled	这项说明启用了bin-log日志功能。参数 log-bin = /data/ats_db/mysql-bin
binlog sync is not enabled, you could loose binlog records during a server crash	没有启用 sync_binlog 选项。也即是将二进制日志实时写入到磁盘通过 sync_binlog=1来指定

worker threads	工作线程
current thread_cache_size = 8	当前线程缓存大小。 thread_concurrency = 8
current threads_cached = 7	show status like ‘threads_cached’
current threads_per_sec = 0	脚本先执行show status like ‘threads_cached’查看当前的线程创建情况，然后sleep 1后在执行相同的命令，最终后者减去前者的数就是每秒线程创建数。
historic threads_per_sec = 0	该值是使用threads_cached /uptime获得的。
your thread_cache_size is fine

max connections	最大连接数
current max_connections = 1024	当前配置文件中设置的并发连接数
current threads_connected = 2	当前线程连接诶数。 show status like ‘threads_connected’
historic max_used_connections = 4	show status like ‘max_used_connections’;
the number of used connections is 0% of the configured maximum.	这个值使用 max_used_connections*100/ max_connections得出。
you are using less than 10% of your configured max_connections. lowering max_connections could help to avoid an over-allocation of memory see “memory usage” section to make sure you are not over-allocating	max_used_connections的值不足max_connections值的10%。设置合适的max_connections值有助于节省内存。

memory usage	内存使用
max memory ever allocated : 841 m	max memory ever allocated = max_memory
configured max per-thread buffers : 28.40 g	configured max per-thread buffers = per_thread_buffers
configured max global buffers : 586 m	configured max global buffers = per_thread_max_buffers
configured max memory limit : 28.97 g	configured max memory limit = total_memory 这一项很重要，他是将各个缓存的大小累加，然后同max_connections相乘，从而得出当达到max_connections后需要分配的内存有多少。我这里由于max_connections写得很大，造成了最大内存限制超过了真实内存很多，所以建议不要随意增大max_connections的值。减小 max_connections的值，最终保证最大内存限制在真实内存的90%以下。
physical memory : 7.79 g	实际物理内存
max memory limit exceeds 90% of physical memory
per_thread_buffers (read_buffer_size read_rnd_buffer_size sort_buffer_size thread_stack join_buffer_size binlog_cache_size)max_connectionsper_thread_max_buffers (read_buffer_size read_rnd_buffer_size sort_buffer_size thread_stack join_buffer_size binlog_cache_size)max_used_connections global_buffers innodb_buffer_pool_size innodb_additional_mem_pool_size innodb_log_buffer_size key_buffer_size query_cache_size max_memory=global_buffers per_thread_max_buffers total_memory=global_buffers per_thread_buffers

key buffer	key 缓冲
current myisam index space = 222 k	当前数据库myisam表中索引占用磁盘空间
current key_buffer_size = 512 m	mysql配置文件中key_buffer_size 设置的大小
key cache miss rate is 1 : 3316	key_read_requests/ key_reads 这里说明3316次读取请求中有1次丢失(也就是说1次读取磁盘)
key buffer free ratio = 81 %	key_blocks_unused * key_cache_block_size / key_buffer_size * 100
your key_buffer_size seems to be fine

query cache	query 缓存
query cache is enabled	该项说明我们指定了query_cache_size 的值。如果query_cache_size=0的话这里给出的提示是： query cache is supported but not enabled perhaps you should set the query_cache_size
current query_cache_size = 64 m	当前系统query_cache_size 值大小 [f]
current query_cache_used = 1 m	query_cache_used =query_cache_size-qcache_free_memory
current query_cache_limit = 128 m	变量 query_cache_limit 大小
current query cache memory fill ratio = 1.79 %	query_cache_used/query_cache_size *100%
current query_cache_min_res_unit = 4 k	show variables like ‘query_cache_min_res_unit’;
your query_cache_size seems to be too high. perhaps you can use these resources elsewhere	这项给出的结论是query_cache_size的值设置的有些过高。其比对标准是 “query cache memory fill ratio”的值如果小于<25%就会给出这个提示。可以将这些资源应用到其他的地方
mysql won’t cache query results that are larger than query_cache_limit in size	mysql不会将大于query_cache_limit的查询结果进行缓存
show status like ‘qcache%’;qcache_free_blocks 10 qcache_free_memory 65891984 qcache_hits 14437 qcache_inserts 707 qcache_lowmem_prunes 0 qcache_not_cached 216 qcache_queries_in_cache 540 qcache_total_blocks 1191

sort operations	sort 选项
current sort_buffer_size = 6 m	show variables like ’sort_buffer%’;
current read_rnd_buffer_size = 16 m	show variables like ‘read_rnd_buffer_size%’;
sort buffer seems to be fine

joins	joins
current join_buffer_size = 132.00 k	show variables like ‘join_buffer_size%’;join_buffer_size= join_buffer_size 4kb
you have had 6 queries where a join could not use an index properly	这里的6是通过 show status like ‘select_full_join’; 获得的
you should enable “log-queries-not-using-indexes” then look for non indexed joins in the slow query log. if you are unable to optimize your queries you may want to increase your join_buffer_size to accommodate larger joins in one pass.note! this script will still suggest raising the join_buffer_size when any joins not using indexes are found.	你需要启用 “log-queries-not-using-indexes” 然后在慢查询日志中看是否有取消索引的joins语句。如果不优化查询语句的话，则需要增大join_buffer_size。

open files limit	文件打开数限制
current open_files_limit = 1234 files	show variables like ‘open_files_limit%’;
the open_files_limit should typically be set to at least 2x-3xthat of table_cache if you have heavy myisam usage.	如果系统中有很多的myisam类型的表，则建议将open_files_limit 设置为2x~3x的table_open_cache show status like ‘open_files’;open_files_ratio= open_files100/open_files_limit 如果open_files_ratio* 超过75% 则需要加大open_files_limit
your open_files_limit value seems to be fine

table cache	table 缓存
current table_open_cache = 512 tables	show variables like ‘table_open_cache’;
current table_definition_cache = 256 tables	show variables like ‘ table_definition_cache ‘;
you have a total of 368 tables	selectcount(*) from information_schema.tables where table_type=’base table’
you have 371 open tables.	show status like ‘open_tables’;
the table_cache value seems to be fine	open_tables /*table_open_cache100%** < 95%
you should probably increase your table_definition_cache value.	table_cache_hit_rate =open_tables*100/opened_tables

temp tables	临时表
current max_heap_table_size = 16 m	show variables like ‘max_heap_table_size’;
current tmp_table_size = 16 m	show variables like ‘tmp_table_size’;
of 285 temp tables, 11% were created on disk	created_tmp_tables=285created_tmp_disk_tables*100/ (created_tmp_tables created_tmp_disk_tables)=11%
created disk tmp tables ratio seems fine

table scans	扫描表
current read_buffer_size = 6 m	show variables like ‘read_buffer_size’;
current table scan ratio = 9 : 1	read_rnd_next =show global status like ‘handler_read_rnd_next’; com_select= show global status like ‘com_select’; full_table_scans=read_rnd_next/com_select current table scan ratio = full_table_scans : 1″ 如果表扫描率超过4000，说明进行了太多表扫描，很有可能索引没有建好，增加read_buffer_size值会有一些好处，但最好不要超过8mb。
read_buffer_size seems to be fine

table locking	table locking
current lock wait ratio = 0 : 5617	show global status like’table_locks_waited’; show global status like ‘questions’; 如果 table_locks_waited=0 current lock wait ratio = 0: questions
your table locking seems to be fine

阅读(1556) | 评论(0) | 转发(0) |

上一篇：mysql运行状态查看

下一篇：mysql高可用凯发app官方网站的解决方案

给主人留下些什么吧！~~

| | | | |

感谢所有关心和支持过chinaunix的朋友们