Heim > Datenbank > MySQL-Tutorial > Mysql中Profiling之性能分析_MySQL

Mysql中Profiling之性能分析_MySQL

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB
Freigeben: 2016-06-01 13:43:57
Original
921 Leute haben es durchsucht

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

Verwandte Etiketten:
Erklärung dieser Website
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn
Beliebte Tutorials
Mehr>
Neueste Downloads
Mehr>
Web-Effekte
Quellcode der Website
Website-Materialien
Frontend-Vorlage