Mysql中Profiling之性能分析

作者:性能测试专栏  发布日期:2012-02-06 21:00:27

MySQL5.0.37版本以上支持了Profiling – 官方手册。此工具可用来查询 SQL 会执行多少时间,System lock和Table lock 花多少时间等等,对定位一条语句的 I/O消耗和CPU消耗 非常重要。
从启动profile之后的所有查询包括错误的语句都会记录。

关闭会话或者set profiling=0 就关闭了。

# 开启 profiling
mysql> set profiling=1;
mysql> select * from user limit 1;
mysql> select count(*) from user group by sexal;
# 查看这些语句的profile
mysql> show profiles;
+--------------+----------------+--------------------------------------------------------------+
| Query_ID | Duration     | Query                                                                      |
+--------------+----------------+--------------------------------------------------------------+
|        1        | 0.00013200   | SELECT DATABASE()                                               |
|        2        | 0.00044100   | select * from user limit 2                                      |
|        3        | 1.95544100   | select nick,count(*) from user group by online|
+--------------+----------------+--------------------------------------------------------------+
查看具体一条(Query_ID=3 这一条)语句的profiles,包括CPU和柱塞I/O的情况
mysql> show profile cpu,block io for query 3;
+----------------------+----------+----------+------------+--------------+---------------+
| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting             | 0.000057 | 0.000000 |   0.000000 |            0 |             0 |
| Opening tables       | 0.000014 | 0.000000 |   0.000000 |            0 |             0 |
| System lock          | 0.000006 | 0.000000 |   0.000000 |            0 |             0 |
| Table lock           | 0.000009 | 0.000000 |   0.000000 |            0 |             0 |
| init                 | 0.000021 | 0.000000 |   0.000000 |            0 |             0 |
| optimizing           | 0.000006 | 0.000000 |   0.000000 |            0 |             0 |
| statistics           | 0.000011 | 0.000000 |   0.000000 |            0 |             0 |
| preparing            | 0.000010 | 0.000000 |   0.000000 |            0 |             0 |
| Creating tmp table   | 0.000039 | 0.000000 |   0.000000 |            0 |             0 |
| executing            | 0.000005 | 0.000000 |   0.000000 |            0 |             0 |
| Copying to tmp table | 1.900619 | 1.030785 |   0.197970 |          127 |           127 |
| Sorting result       | 0.000015 | 0.000000 |   0.000000 |            0 |             0 |
| Sending data         | 0.000015 | 0.000000 |   0.000000 |            0 |             0 |
| end                  | 0.000005 | 0.000000 |   0.000000 |            0 |             0 |
| removing tmp table   | 0.000008 | 0.000000 |   0.000000 |            0 |             0 |
| end                  | 0.000005 | 0.000000 |   0.000000 |            0 |             0 |
| query end            | 0.000004 | 0.000000 |   0.000000 |            0 |             0 |
| freeing items        | 0.000025 | 0.000000 |   0.000000 |            0 |             0 |
| logging slow query   | 0.000004 | 0.000000 |   0.000000 |            0 |             0 |
| cleaning up          | 0.000005 | 0.000000 |   0.000000 |            0 |             0 |
+----------------------+----------+----------+------------+--------------+---------------+
上面就获得了一条语句的CPU和Block IO消耗,对定位瓶颈很方便,其余的一些信息,可以用语句:“Show profile *** for query 3”来获取

 

 

另外附上profiling 的学习质料


•profiling 基础
•http://www.xaprb.com/blog/2006/10/12/how-to-profile-a-query-in-mysql/
•http://www.xaprb.com/blog/2006/10/15/a-case-study-in-profiling-queries-in-mysql/
•http://www.xaprb.com/blog/2006/10/17/mysql-profiling-case-study-part-2/
•profiling 进阶
•http://www.mysqlperformanceblog.com/2009/01/19/profiling-mysql-stored-routines/
•http://www.mysqlperformanceblog.com/2008/05/18/wanted-better-memory-profiling-for-mysql/
•maatkit–perldoc mk-query-profiler
•profiling高级
•http://dev.mysql.com/tech-resources/articles/pro-mysql-ch6.html
•http://dev.mysql.com/tech-resources/articles/using-new-query-profiler.html
•profiling骨灰级
•http://www.scribd.com/doc/2669413/DTrace-and-MySQL-Presentation
•http://forge.mysql.com/wiki/Using_DTrace_with_MySQL
•http://wikis.sun.com/display/BluePrints/Optimizing+MySQL+Database+Application+Performan

Tag标签: Mysql   Profiling   性能分析  
  • 专题推荐

About IT165 - 广告服务 - 隐私声明 - 版权申明 - 免责条款 - 网站地图 - 网友投稿 - 联系方式
本站内容来自于互联网,仅供用于网络技术学习,学习中请遵循相关法律法规