SHOW PROFILES检测mysql的性能方法
星期一, 2012-01-23 | Author: Lee | Database, JAVA-and-J2EE | 5,997 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;
文章作者: Lee
本文地址: https://www.pomelolee.com/886.html
除非注明,Pomelo Lee文章均为原创,转载请以链接形式标明本文地址
No comments yet.
Leave a comment
Search
相关文章
热门文章
最新文章
文章分类
- ajax (10)
- algorithm-learn (3)
- Android (6)
- as (3)
- computer (85)
- Database (30)
- disucz (4)
- enterprise (1)
- erlang (2)
- flash (5)
- golang (3)
- html5 (18)
- ios (4)
- JAVA-and-J2EE (186)
- linux (143)
- mac (10)
- movie-music (11)
- pagemaker (36)
- php (50)
- spring-boot (2)
- Synology群晖 (2)
- Uncategorized (6)
- unity (1)
- webgame (15)
- wordpress (33)
- work-other (2)
- 低代码 (1)
- 体味生活 (40)
- 前端 (21)
- 大数据 (8)
- 游戏开发 (9)
- 爱上海 (19)
- 读书 (4)
- 软件 (3)