mysql show profile详解_show profiles 详解

12.5.5.29. SHOW PROFILES Syntax

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, althought there

might be some minor differences in interpretion for the two

statements for some status values (see

Section 7.5.6, “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 19.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.

from：http://dev.mysql.com/doc/refman/5.0/en/show-profiles.html