MySQL的性能调优工具:比mysqlreport更方便的tuning-primer.sh
作者:车东 来源:车东博客 发布时间:2008-12-08 08:37:00
年初的时候收藏过一篇关于mysqlreport的报表解读,和内置的show status,和show variables相比mysqlreport输出一个可读性更好的报表;但Sundry MySQL提供的脚本相比mysqlreport更进一步:除了报表还进一步提供了修改建议。安装和使用非常简单:
wget http://www.day32.com/MySQL/tuning-primer.sh
chmod +x tuning-primer.sh
./tuning-primer.sh
和mysqlreport一样,tuning-primer.sh也支持.my.cnf
[client]
user = USERNAME
password = PASSWORD
socket = /tmp/mysql.sock
样例输出:在终端上按照问题重要程度分别用黄色/红色字符标记问题
-- MYSQL PERFORMANCE TUNING PRIMER --
- By: Matthew Montgomery -
MySQL Version 5.0.45 i686
Uptime = 19 days 8 hrs 32 min 54 sec
Avg. qps = 0
Total Questions = 264260
Threads Connected = 1Server has been running for over 48hrs.
It should be safe to follow these recommendationsTo find out more information on how each of these
runtime variables effects performance visit:
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html
Visit http://www.mysql.com/products/enterprise/advisors.html
for info about MySQL's Enterprise Monitoring and Advisory ServiceSLOW QUERIES
The slow query log is NOT enabled.
Current long_query_time = 10 sec.
You have 0 out of 264274 that take longer than 10 sec. to complete
Your long_query_time may be too high, I typically set this under 5 sec.BINARY UPDATE LOG
The binary update log is NOT enabled.
You will not be able to do point in time recovery
See http://dev.mysql.com/doc/refman/5.0/en/point-in-time-recovery.htmlWORKER THREADS
Current thread_cache_size = 0
Current threads_cached = 0
Current threads_per_sec = 1
Historic threads_per_sec = 0
Your thread_cache_size is fineMAX CONNECTIONS
Current max_connections = 100
Current threads_connected = 1
Historic max_used_connections = 33
The number of used connections is 33% of the configured maximum.
Your max_connections variable seems to be fine.MEMORY USAGE
Max Memory Ever Allocated : 96 M
Configured Max Per-thread Buffers : 268 M
Configured Max Global Buffers : 7 M
Configured Max Memory Limit : 276 M
Physical Memory : 1.97 G
Max memory limit seem to be within acceptable normsKEY BUFFER
Current MyISAM index space = 8 M
Current key_buffer_size = 7 M
Key cache miss rate is 1 : 1817
Key buffer fill ratio = 6.00 %
Your key_buffer_size seems to be too high.
Perhaps you can use these resources elsewhereQUERY CACHE
Query cache is supported but not enabled
Perhaps you should set the query_cache_sizeSORT OPERATIONS
Current sort_buffer_size = 2 M
Current read_rnd_buffer_size = 256 K
Sort buffer seems to be fineJOINS
Current join_buffer_size = 132.00 K
You have had 0 queries where a join could not use an index properly
Your joins seem to be using indexes properlyOPEN FILES LIMIT
Current open_files_limit = 1024 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
Your open_files_limit value seems to be fineTABLE CACHE
Current table_cache value = 64 tables
You have a total of 125 tables
You have 64 open tables.
Current table_cache hit rate is 9%, while 100% of your table cache is in use
You should probably increase your table_cache
TEMP TABLES
Current max_heap_table_size = 16 M
Current tmp_table_size = 32 M
Of 564 temp tables, 6% were created on disk
Effective in-memory tmp_table_size is limited to max_heap_table_size.
Created disk tmp tables ratio seems fineTABLE SCANS
Current read_buffer_size = 128 K
Current table scan ratio = 1 : 1
read_buffer_size seems to be fineTABLE LOCKING
Current Lock Wait ratio = 0 : 264392
Your table locking seems to be fine
更有用是作者总结的处理MySQL性能问题处理的优先级:尤其是头3条,基本上可以解决大部分瓶颈问题的原因。
# Slow Query Log 慢查询 尤其是like操作,性能杀手,轻易不要使用,让全文索引交给Lucene或者利用Tag机制减少like操作;
# Max Connections 并发连接数:一个MySQL deamon缺省最大连接数是100,调到更高只是为了出现问题是给我们更多的缓冲时间而不是任其一直处于那么高的状态,并发连接数类似于等候大厅:当等候人数过多的时候,一味扩大等候厅不是根本解决问题的办法,提高业务的处理速度,多开几个窗口才是更好的解决方法;我的经验就是超过100: 数据就要想办法(镜像或者分片)分布到更多Deamon上;
# Worker Threads: Jeremy Zawondy 曾在部落格上說到:Thread caching 並不是我們最需要關心的問題,但當你解決了所有其他更嚴重的問題之後,它就會是最嚴重的問題。(thread caching really wasn't the worst of our problems. But it became the worst after we had fixed all the bigger ones.)
# Key Buffer
# Query Cache
# Sort Buffer
# Joins
# Temp Tables 临时表
# Table (Open & Definition) Cache 表缓存;
# Table Locking 表锁定
# Table Scans (read_buffer)
# Innodb Status
其他一些工具:
1
mytop: 一个top like的show processlist;
2 使用cacti做MySQL的监控:推荐配置模板;
3 把binlog导出成文本和slowquery的格式几乎是一样的,调用mysqlslowquery脚本分析,有时候也会有意外收获;
猜你喜欢
- 以一种有意义的方式组织数据可能是一项挑战。有时你需要的可能是一个简单的排序,但是通常你需要做更多,你需要分组来进行分析和统计。幸运的是,SQ
- 一.ASP使用SQL查询数据库方法: 方法1 Set&nbs
- 设计首页的第一步是设计版面布局。就象传统的报刊杂志编辑一样,我们将网页看作一张报纸,一本杂志来进行排版布局。虽然 * 页技术的发展使得我们开
- 看到这篇文章的人,如果我没有估计错的话,八成是从事互联网的人,其中又有八成是做设计的。如果真是这样,可以一起庆幸一下。首先,庆幸从事着一个没
- <%@LANGUAGE="xxx" CODEPAGE="936"%>一般又分为<%
- <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
- 问题:SQL Server 2000中设计表时如何得到自动编号字段?解答:具体步骤如下:①像Access中的自动编号字段右键你的表-->
- 以前经常吃公司旁边的食堂,人多,排队。夏天的时候,我们总要找一个靠窗口通风好的地方坐,没有空调只有风扇,风扇很多,开关都集中在一个地方,应该
- [编者注:]提起数据库,第一个想到的公司,一般都会是Oracle(即甲骨文公司)。Oracle在数据库领域一直处于领先地位。Oracle关系
- 首先让我们看下 YUI 是如何处理的:var toObject = function(a) { var o = {
- 1. 鼠标的哪个按键被点击?<html><head><script type="text/javas
- 比如说点的是图片的左边,还是右边,上边还是下边?点击图片左右显示上下张,我怎么知道?这样就可以做出像QQ空间那样,打开上一个图片和下一个图片
- ExpiresAbsolute 属性指定缓存于浏览器中的页的到期日期和时间语法Response.ExpiresAbsolute [
- 在 MySQL下,在进行中文模糊检索时,经常会返回一些与之不相关的记录,如查找 "%a%" 时,返回的可能有中文字符,却
- 我们大家都知道CSS功能的强大,而有关CSS基本的排版控制虽然已有详细的使用说明和参考教程,但还有许多丰富的CSS排版能力,是很少能查到的。
- 知识补充表单简介(来自Mr._Dang)action:提交的地址method:提交的方式 get: 参数是在url中的,不安全,传输量比较少
- 如下图,我们在做图片logo列表的时候通常是用li标签来实现。html:<ul class="logolist&q
- 1.创建数据库连接,并打开set cnn=Server.CreateObject("ADODB.Connection&q
- 前言:最近某个时间开始,特别留意了一下Web标准中柱状图,也就是英文中的bar graph的实现。虽然实现方法各异,效果不尽相同,但是总体来
- 1.不装入数据库而启动事例 可以不装入数据库而启动事例,一般是在数据库才创建时才可以这样做:STARTUP NOMOUNT2.启动事例并装入