Mysql中Profiling之性能分析

来源：互联网发布：西安淘宝设计代运营编辑：程序博客网时间：2024/05/01 11:18

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