MySQL 5.6 Reference Manual Including MySQL NDB Cluster 7.3-7.4 Reference Guide
The following example demonstrates how to use Performance Schema
statement events and stage events to retrieve data comparable to
profiling information provided by SHOW
PROFILES
and SHOW
PROFILE
statements.
In this example, statement and stage event data is collected in
the events_statements_history_long
and events_stages_history_long
tables. On a busy server with many active foreground threads,
data could age out of the history tables before you are able to
retrieve the information you want to analyze. If you encounter
this problem, options include:
Running the query on a test instance where there is less foreground thread activity.
Disabling instrumentation for other existing foreground
threads by setting the INSTRUMENTED
field
of the threads
table to
NO
for other thread records. For example,
the following statement disables instrumentation for all
foreground threads except the test_user
thread:
mysql>UPDATE performance_schema.threads
SET INSTRUMENTED = 'NO'
WHERE TYPE='FOREGROUND' AND PROCESSLIST_USER NOT LIKE 'test_user';
However, be aware that new threads are always instrumented by default.
Increasing the number of rows in the
events_statements_history_long
and events_stages_history_long
tables. The
performance_schema_events_statements_history_size
and
performance_schema_events_stages_history_size
configuration options are autosized by default but can also
be set explicitly at startup. You can view current settings
by running SHOW VARIABLES
.
For information about autosized Performance Schema
parameters, see
Section 22.3, “Performance Schema Startup Configuration”.
Performance Schema displays event timer information in
picoseconds (trillionths of a second) to normalize timing data
to a standard unit. In the following example,
TIMER_WAIT
values are divided by
1000000000000 to show data in units of seconds. Values are also
truncated to 6 decimal places to display data in the same format
as SHOW PROFILES
and
SHOW PROFILE
statements.
Ensure that statement and stage instrumentation is enabled
by updating the
setup_instruments
table. Some
instruments may already be enabled by default.
mysql>UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE '%statement/%';
mysql>UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE '%stage/%';
Ensure that events_statements_*
and
events_stages_*
consumers are enabled.
Some consumers may already be enabled by default.
mysql>UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES'
WHERE NAME LIKE '%events_statements_%';
mysql>UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES'
WHERE NAME LIKE '%events_stages_%';
Run the statement that you want to profile. For example:
mysql> SELECT * FROM employees.employees WHERE emp_no = 10001;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-----------+--------+------------+
| 10001 | 1953-09-02 | Georgi | Facello | M | 1986-06-26 |
+--------+------------+------------+-----------+--------+------------+
Identify the EVENT_ID
of the statement by
querying the
events_statements_history_long
table. This step is similar to running
SHOW PROFILES
to identify the
Query_ID
. The following query produces
output similar to SHOW
PROFILES
:
mysql> SELECT EVENT_ID, TRUNCATE(TIMER_WAIT/1000000000000,6) as Duration, SQL_TEXT
FROM performance_schema.events_statements_history_long WHERE SQL_TEXT like '%10001%';
+----------+----------+--------------------------------------------------------+
| event_id | duration | sql_text |
+----------+----------+--------------------------------------------------------+
| 31 | 0.028310 | SELECT * FROM employees.employees WHERE emp_no = 10001 |
+----------+----------+--------------------------------------------------------+
Query the
events_stages_history_long
table to retrieve the statement's stage events. Stages are
linked to statements using event nesting. Each stage event
record has a NESTING_EVENT_ID
column that
contains the EVENT_ID
of the parent
statement.
mysql> SELECT event_name AS Stage, TRUNCATE(TIMER_WAIT/1000000000000,6) AS Duration
FROM performance_schema.events_stages_history_long WHERE NESTING_EVENT_ID=31;
+--------------------------------+----------+
| Stage | Duration |
+--------------------------------+----------+
| stage/sql/starting | 0.000080 |
| stage/sql/checking permissions | 0.000005 |
| stage/sql/Opening tables | 0.027759 |
| stage/sql/init | 0.000052 |
| stage/sql/System lock | 0.000009 |
| stage/sql/optimizing | 0.000006 |
| stage/sql/statistics | 0.000082 |
| stage/sql/preparing | 0.000008 |
| stage/sql/executing | 0.000000 |
| stage/sql/Sending data | 0.000017 |
| stage/sql/end | 0.000001 |
| stage/sql/query end | 0.000004 |
| stage/sql/closing tables | 0.000006 |
| stage/sql/freeing items | 0.000272 |
| stage/sql/cleaning up | 0.000001 |
+--------------------------------+----------+