MySQL performance_schema

performance_schema.sql

/* profiling */
set profiling = 1;
show profiles;
show profile for query X;


/* performance_schema configure */
SELECT * FROM performance_schema.setup_instruments;

/* performance_schema results */
SELECT *  FROM performance_schema.events_statements_summary_by_digest ORDER BY SUM_TIMER_WAIT DESC LIMIT 20\G
SELECT * FROM performance_schema.threads\G

// tune what you need - see actors 
UPDATE performance_schema.threads SET instrumented = 'NO';

SELECT event_name, timer_wait/1000000000 wait_ms 
    FROM events_stages_history_long AS stages 
       	    JOIN (SELECT event_id 
                	         FROM events_statements_history_long 
               	         ORDER BY event_id DESC limit 1) AS statements 
          ON stages.nesting_event_id = statements.event_id 
        	  ORDER BY stages.event_id;


SELECT thread_id, 
         CONCAT( CASE WHEN event_name LIKE 'stage%' THEN
                       CONCAT('  ', event_name)
                     WHEN event_name LIKE 'wait%' AND nesting_event_id IS NOT NULL THEN
                       CONCAT('    ', event_name)
                     ELSE IF(digest_text IS NOT NULL, SUBSTR(digest_text, 1, 64), event_name)
               END,
               ' (',ROUND(timer_wait/1000000, 2),'μ) ') event
   FROM (
         (SELECT thread_id, event_id, event_name, 
                 timer_wait, timer_start, nesting_event_id, digest_text
            FROM events_statements_history_long)
         UNION
         (SELECT thread_id, event_id, event_name,
                 timer_wait, timer_start, nesting_event_id, NULL
            FROM events_stages_history_long)
         UNION
         (SELECT thread_id, event_id, event_name, 
                 timer_wait, timer_start, nesting_event_id, NULL
            FROM events_waits_history_long)
        ) events
  ORDER BY thread_id, event_id;