SHOW PROFILE [type
[,type
] ... ] [FOR QUERYn
] [LIMITrow_count
[OFFSEToffset
]]type
: ALL | BLOCK IO | CONTEXT SWITCHES | CPU | IPC | MEMORY | PAGE FAULTS | SOURCE| SWAPS
Profiling is controlled by the
profiling
session variable, which has a default value of 0 (OFF
). Profiling is enabled by settingprofiling
to 1 orON
:mysql>
SET profiling = 1;
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 countsProfiling 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)NoteProfiling 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.You can also get profiling information from the
PROFILING
table inINFORMATION_SCHEMA
. See Section 22.18, “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;
show profile 分析性能
最新推荐文章于 2022-03-21 09:17:38 发布