mysql profiling_MySQL profiling性能分析

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

摘自 性能测试专栏