content_image/696733.jpg content_image/696742.jpg content_image/696734.jpg
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/%’; 

3. 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_%’; 

4. Under the user account you are monitoring, run the statement that you want to profile. For example: 

mysql> SELECT * FROM employees.employees WHERE emp_no = 10001; 

<img src='content_image/696733.jpg'>

5. 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%’; 

<img src='content_image/696742.jpg'>

6. 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; 

<img src='content_image/696734.jpg'>

