ShowProfileDocs
Contents |
SHOW PROFILES
Stub syntax for community patch submitted by Jeremy Cole.
(UPDATE 2007-03-08) Now in the official manual:
Syntax
SHOW PROFILES
SHOW PROFILE [types] [FOR QUERY n] [LIMIT n [OFFSET n] ]
where "n" is an integer
and "types" is zero or many (comma-separated) of
"CPU"
"MEMORY" (not presently supported)
"BLOCK IO"
"CONTEXT SWITCHES"
"PAGE FAULTS"
"IPC"
"SWAPS"
"SOURCE"
"ALL"
Profiling is disabled by default. It can be enabled using the profiling session variable.
SHOW PROFILES displays a list of the last 15 queries sent to the master. The list can be resized using the profiling_history_size session variable. The default is 15. The maximum value is 100. Setting it to 0 will have the practical effect of disabling profiling.
SHOW PROFILE without options will refer to the last query executed.
All queries are profiled, except SHOW PROFILES(S). Incorrect queries are profiled as well. This means that you won't find SHOW PROFILE in the profiles list, but SHOW PROFILING will be there (and you will get a syntax error while executing it, of course).
Profiling is enabled by session. When your session ends, all profiling information is lost.
Availability
SHOW PROFILES is available from MySQL Community Tree 5.0.37. It is not available in MySQL Enterprise releases (as of current 5.0.36).
To build from source, follow the instructions at Installing from the Development Source Tree using the Community tree.
bkf clone bk://mysql.bkbits.net/mysql-5.0-community mysql-5.0
INFORMATION_SCHEMA.PROFILING
There is a companion table for profiling. INFORMATION_SCHEMA.PROFILING contains the profiling detail of all the queries in your list. You can query it instead of using SHOW PROFILE.
For example, the following queries give the same result (well, they will, when Bug#26600 is fixed):
show profile for query 2; select state, format(duration,6) as duration from information_schema.profiling where query_id = 2 order by seq;
Examples
select @@profiling; +-------------+ | @@profiling | +-------------+ | 0 | +-------------+ 1 row in set (0.00 sec) set profiling =1; Query OK, 0 rows affected (0.00 sec) drop table if exists t1; Query OK, 0 rows affected, 1 warning (0.00 sec) create table t1 (id int); Query OK, 0 rows affected (0.01 sec) 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) 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) show profile for query 2; +----------------------+----------+ | 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) 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) 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) show profile source for query 2; +----------------------+----------+-----------------------+--------------+-------------+ | Status | Duration | Source_function | Source_file | Source_line | +----------------------+----------+-----------------------+--------------+-------------+ | checking permissions | 0.000040 | check_access | sql_parse.cc | 5179 | | creating table | 0.000056 | mysql_create_table | sql_table.cc | 1732 | | After create | 0.011363 | mysql_create_table | sql_table.cc | 1763 | | query end | 0.000375 | mysql_execute_command | sql_parse.cc | 5008 | | freeing items | 0.000089 | mysql_parse | sql_parse.cc | 5896 | | logging slow query | 0.000019 | log_slow_statement | sql_parse.cc | 2166 | | cleaning up | 0.000005 | dispatch_command | sql_parse.cc | 2131 | +----------------------+----------+-----------------------+--------------+-------------+ 7 rows in set (0.00 sec)
Notes
Profiling is only partially functional on some architectures. Where there is no getrusage() system call, presently Null values are returned where it would be required. Notably, Windows needs some love applied to make it as useful.
References
Blog posts
- MySQL, with SHOW PROFILE and updated INFORMATION_SCHEMA, built from the Community tree
- Test driving show profiles
Work logs
Known Bugs
(Anyone wants to pick up a bug? See the guidelines at Contributing)