> 데이터 베이스 > MySQL 튜토리얼 > Mysql中Profiling之性能分析_MySQL

Mysql中Profiling之性能分析_MySQL

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB
풀어 주다: 2016-06-01 13:43:57
원래의
924명이 탐색했습니다.

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

摘自 性能测试专栏 bitsCN.com

관련 라벨:
본 웹사이트의 성명
본 글의 내용은 네티즌들의 자발적인 기여로 작성되었으며, 저작권은 원저작자에게 있습니다. 본 사이트는 이에 상응하는 법적 책임을 지지 않습니다. 표절이나 침해가 의심되는 콘텐츠를 발견한 경우 admin@php.cn으로 문의하세요.
인기 튜토리얼
더>
최신 다운로드
더>
웹 효과
웹사이트 소스 코드
웹사이트 자료
프론트엔드 템플릿