mysql -> show profile ;
来源:互联网 发布:淘宝模特兼职是真的吗 编辑:程序博客网 时间:2024/06/06 19:57
mysql用于分析sql的性能。可以使用explain以及profile。下面是show profile 详解。
This section does not apply to MySQL Enterprise Server users.
SHOW PROFILE [type [, type] ... ]
[FOR QUERY n]
[LIMIT row_count [OFFSET offset]]
type:
ALL
| BLOCK IO
| CONTEXT SWITCHES
| CPU
| IPC
| MEMORY
| PAGE FAULTS
| SOURCE
| SWAPS
The SHOW PROFILES and SHOW PROFILE statements display profiling information that indicates resource usage for statements executed during the course of the current session.
Profiling is controlled by the profiling session variable, which has a default value of 0 (OFF). Profiling is enabled by setting profiling to 1 or ON:
mysql> SET profiling = 1;
SHOW PROFILES displays a list of the most recent statements sent to the master. The size of the list is controlled by the profiling_history_size session variable, which has a default value of 15. The maximum value is 100. Setting the value to 0 has the practical effect of disabling profiling.
All statements are profiled except SHOW PROFILES and SHOW PROFILE, so you will find neither of those statements in the profile list. Malformed statements are profiled. For example, SHOW PROFILING is an illegal statement, and a syntax error occurs if you try to execute it, but it will show up in the profiling list.
SHOW PROFILE displays detailed information about a single statement. Without the FOR QUERY n clause, the output pertains to the most recently executed statement. If FOR QUERY n is included, SHOW PROFILE displays information for statement n. The values of n correspond to the Query_ID values displayed by SHOW PROFILES.
The LIMIT row_count clause may be given to limit the output to row_count rows. If LIMIT is given, OFFSET offset may be added to begin the output offset rows into the full set of rows.
By default, SHOW PROFILE displays Status and Duration columns. The Status values are like the State values displayed by SHOW PROCESSLIST, although there might be some minor differences in interpretion for the two statements for some status values (see Section 7.10, “Examining Thread Information”).
Optional type values may be specified to display specific additional types of information:
ALL displays all information
BLOCK IO displays counts for block input and output operations
CONTEXT SWITCHES displays counts for voluntary and involuntary context switches
CPU displays user and system CPU usage times
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 is enabled per session. When a session ends, its profiling information is lost.
mysql> SELECT @@profiling;
+-------------+
| @@profiling |
+-------------+
| 0 |
+-------------+
1 row in set (0.00 sec)
mysql> SET profiling = 1;
Query OK, 0 rows affected (0.00 sec)
mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> CREATE TABLE T1 (id INT);
Query OK, 0 rows affected (0.01 sec)
mysql> SHOW PROFILES;
+----------+----------+--------------------------+
| Query_ID | Duration | Query |
+----------+----------+--------------------------+
| 0 | 0.000088 | SET PROFILING = 1 |
| 1 | 0.000136 | DROP TABLE IF EXISTS t1 |
| 2 | 0.011947 | CREATE TABLE t1 (id INT) |
+----------+----------+--------------------------+
3 rows in set (0.00 sec)
mysql> SHOW PROFILE;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| checking permissions | 0.000040 |
| creating table | 0.000056 |
| After create | 0.011363 |
| query end | 0.000375 |
| freeing items | 0.000089 |
| logging slow query | 0.000019 |
| cleaning up | 0.000005 |
+----------------------+----------+
7 rows in set (0.00 sec)
mysql> SHOW PROFILE FOR QUERY 1;
+--------------------+----------+
| Status | Duration |
+--------------------+----------+
| query end | 0.000107 |
| freeing items | 0.000008 |
| logging slow query | 0.000015 |
| cleaning up | 0.000006 |
+--------------------+----------+
4 rows in set (0.00 sec)
mysql> SHOW PROFILE CPU FOR QUERY 2;
+----------------------+----------+----------+------------+
| Status | Duration | CPU_user | CPU_system |
+----------------------+----------+----------+------------+
| checking permissions | 0.000040 | 0.000038 | 0.000002 |
| creating table | 0.000056 | 0.000028 | 0.000028 |
| After create | 0.011363 | 0.000217 | 0.001571 |
| query end | 0.000375 | 0.000013 | 0.000028 |
| freeing items | 0.000089 | 0.000010 | 0.000014 |
| logging slow query | 0.000019 | 0.000009 | 0.000010 |
| cleaning up | 0.000005 | 0.000003 | 0.000002 |
+----------------------+----------+----------+------------+
7 rows in set (0.00 sec)
Note
Profiling is only partially functional on some architectures. For values that depend on the getrusage() system call, NULL is returned on systems such as Windows that do not support the call. In addition, profiling is per process and not per thread. This means that activity on threads within the server other than your own may affect the timing information that you see.
SHOW PROFILES and SHOW PROFILE were added in MySQL 5.0.37.
You can also get profiling information from the PROFILING table in INFORMATION_SCHEMA. See Section 18.17, “The INFORMATION_SCHEMA PROFILING Table”. For example, the following queries produce the same result:
SHOW PROFILE FOR QUERY 2;
SELECT STATE, FORMAT(DURATION, 6) AS DURATION
FROM INFORMATION_SCHEMA.PROFILING
WHERE QUERY_ID = 2 ORDER BY SEQ;
Important
Please note that the SHOW PROFILE and SHOW PROFILES functionality is part of the MySQL 5.0 Community Server only.
- mysql -> show profile ;
- mysql的show profile
- mysql show profile
- mysql show profile
- Mysql show profile 详解
- mysql 下 show profile 备忘
- mysql性能分析工具--show profile
- MySql 使用SHOW PROFILE 进行SQL 分析
- show profile使用方法查看mysql性能
- MySql 使用SHOW PROFILE 进行SQL 分析
- mysql查询性能分析(show status,show profile,explain)
- MySQL性能分析语句 show profile/show profiles
- show profile;
- mysql性能分析工具show profile(翻译)
- Mysql单条查询性能剖析 show profile
- mysql中使用 SHOW PROFILE与 SHOW STATUS做单条语句剖析
- SHOW PROFILE参数
- show profile的使用
- java中常见的日期时间类
- 第八周实验报告3
- 开始有目标的学习,努力!
- AS3 ComboBox ---- 下拉列表框类,又是细节是魔鬼啊
- 【GLSL教程】(八)纹理贴图
- mysql -> show profile ;
- Filter过滤器
- Go-Home – 12306 网上火车票自动订票程序
- 【GLSL教程】(九)其他说明
- stm32串口1收发
- hdoj1010
- jquery easyui table 典型应用
- 使用VC的Picture control在窗体上显示一副图片
- Rtems--c_user--系统配置