Pomelo Lee workspace

SHOW PROFILES检测mysql的性能方法

星期一, 2012-01-23 | Author: LeecloseAuthor: Lee Name: lee
Email: [email protected]
Site: https://www.pomelolee.com/
About: webgame网页,手机游戏服务端架构和开发者See Authors Posts (492) | Database, JAVA-and-J2EE | 6,001 views

在网上看使用sql的执行效率的mysql检测方法，实际上有更好的基于aop的方法进行所有的sql的效率执行效果，并不限于mysql还是oracle,不过有点影响效率，也可以关闭次方法，在dao级别进行拦截控制，明确每个的sql的执行效率.在阿里巴巴有个dao的开源框架，很不错的，需要的话可以猛点击Druid,这个不是本文记录要点啦.
MySQL Query Profile MySQL 5.0.37 以上开始支持 MySQL Query Profiler, 可以查询到此 SQL 会执行多少时间, 并看出 CPU/Memory 使用量, 执行过程中 System lock, Table lock 花多少时间等等.

详细可以参见官方文档：SHOW PROFILES Syntax

启动

mysql> set profiling=1;

Query OK, 0 rows affected (0.00 sec)

测试查询

mysql> select count(*) from client where broker_id=2;

+———-+

| count(*) |

+———-+

| 200 |

+———-+

1 row in set (0.00 sec)

查看profiles

mysql> show profiles;

+———-+————+———————————————–+

| Query_ID | Duration | Query |

+———-+————+———————————————–+

| 0 | 0.00007300 | set profiling=1 |

| 1 | 0.00044700 | select count(*) from client where broker_id=2 |

+———-+————+———————————————–+

2 rows in set (0.00 sec)

查看单条profile 查询的query 1 是第1条，不写的话是默认最后一条

mysql> show profile for query 1;

mysql> alter table t engine=myisam;

Query OK, 112050 rows affected (0.64 sec)

Records: 112050 Duplicates: 0 Warnings: 0

mysql> show profiles;

mysql> show profile for query 4;

查看cpu资源等信息

mysql> show profile cpu for query 4;

mysql> show profile;
其他属性列表

* ALL – displays all information

* BLOCK IO – displays counts for block input and output operations

* CONTEXT SWITCHES – displays counts for voluntary and involuntary context switches

* IPC – displays counts for messages sent and received

* MEMORY – is not currently implemented

* PAGE FAULTS – displays counts for major and minor page faults

* SOURCE – displays the names of functions from the source code, together with the name and line number of the file in which the function occurs

* SWAPS – displays swap counts

设定profiling保存size

mysql> show variables where variable_name=’profiling_history_size’; # 默认15条

关闭
mysql> set profiling=0;

Tags: Druid, msyql, mysql性能, PROFILE, PROFILES

No comments yet.

Leave a comment