注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

淘尽网 官方博客

淘尽网http://www.tao3w.com做最好的比价网站

 
 
 

日志

 
 
关于我

淘尽网 http://www.tao3w.com 做最好的比价网站,做最好的数据抓取专家。

网易考拉推荐

MYSQL的profile使用  

2014-05-10 19:50:22|  分类: MYSQL |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
MYSQL的profiling功能要在Mysql版本5.0.37以上才能使用。

开启profiling:
set profiling=1;

查看是否设置生效:
select @@profiling;
默认是0,设置成功是1

运行SQL语句:
mysql> select * FROM hx_line WHERE id = '1455023';

查看profiles
mysql> show profiles;
+----------+------------+---------------------------------------------+
| Query_ID | Duration   | Query                                       |
+----------+------------+---------------------------------------------+
|        1 | 0.00036150 | select * FROM hx_line WHERE id = '1455023' |
+----------+------------+---------------------------------------------+

查看具体某条的profile

mysql> show profile FOR QUERY 1;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000013 |
| Waiting for query cache lock   | 0.000014 |
| checking query cache for query | 0.000038 |
| checking permissions           | 0.000006 |
| Opening tables                 | 0.000013 |
| System lock                    | 0.000009 |
| Waiting for query cache lock   | 0.000024 |
| init                           | 0.000060 |
| optimizing                     | 0.000014 |
| statistics                     | 0.000046 |
| preparing                      | 0.000017 |
| executing                      | 0.000004 |
| Sending data                   | 0.000081 |
| end                            | 0.000005 |
| query end                      | 0.000004 |
| closing tables                 | 0.000008 |
| freeing items                  | 0.000009 |
| Waiting for query cache lock   | 0.000003 |
| freeing items                  | 0.000013 |
| Waiting for query cache lock   | 0.000003 |
| freeing items                  | 0.000003 |
| storing result in query cache  | 0.000005 |
| logging slow query             | 0.000003 |
| cleaning up                    | 0.000004 |
+--------------------------------+----------+
24 rows

我们看到了一个简单的查询,MYSQL内部做了24次操作。
另外,看到了一堆query cache的操作,试着把query_cache_size=0,把query_cache关闭,再次测试:


mysql> show profile FOR QUERY 1;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000040 |
| checking permissions | 0.000007 |
| Opening tables       | 0.000015 |
| System lock          | 0.000010 |
| init                 | 0.000061 |
| optimizing           | 0.000013 |
| statistics           | 0.000059 |
| preparing            | 0.000018 |
| executing            | 0.000004 |
| Sending data         | 0.000092 |
| end                  | 0.000006 |
| query end            | 0.000004 |
| closing tables       | 0.000008 |
| freeing items        | 0.000020 |
| logging slow query   | 0.000003 |
| cleaning up          | 0.000004 |
+----------------------+----------+
16 rows in set (0.00 sec)

当开启了query_cache的情况下,需要多操作6次,在这个示例里面多化了0.000087s。

查询这条语句对CPU的使用情况:
mysql> show profile cpu FOR QUERY 1;
+----------------------+----------+----------+------------+
| Status               | Duration | CPU_user | CPU_system |
+----------------------+----------+----------+------------+
| starting             | 0.000037 | 0.000000 |   0.000000 |
| checking permissions | 0.000009 | 0.000000 |   0.000000 |
| Opening tables       | 0.000014 | 0.000000 |   0.000000 |
| System lock          | 0.000009 | 0.000000 |   0.000000 |
| init                 | 0.000059 | 0.000000 |   0.000000 |
| optimizing           | 0.000009 | 0.000000 |   0.000000 |
| statistics           | 0.000044 | 0.000000 |   0.000000 |
| preparing            | 0.000015 | 0.000000 |   0.000000 |
| executing            | 0.000004 | 0.000000 |   0.000000 |
| Sending data         | 0.000081 | 0.000000 |   0.000000 |
| end                  | 0.000006 | 0.000000 |   0.000000 |
| query end            | 0.000004 | 0.000000 |   0.000000 |
| closing tables       | 0.000008 | 0.000000 |   0.000000 |
| freeing items        | 0.000021 | 0.000000 |   0.000000 |
| logging slow query   | 0.000004 | 0.000000 |   0.000000 |
| cleaning up          | 0.000004 | 0.000000 |   0.000000 |
+----------------------+----------+----------+------------+

show profile额外一些命令:
* ALL - displays all information
* BLOCK IO - displays counts for block input and output Operations
* CONTEXT SWITCHES - displays counts for voluntary and involuntary context switches
* ipC - displays counts for messages sent and received
* MEMORY - is not currently implemented
* PAGE FAULTS - displays counts for major and minor page faults
* SOURCE - displays the names of functions from the source code, together with the name and line number of the file in which the function occurs
* SWAPS - displays swap counts
  评论这张
 
阅读(59)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017