mysql performance schema

perf_schema.sql

# verify if its on/off
SHOW VARIABLES LIKE 'perf%';

# List frequently running queries

select * from events_statements_summary_by_digest order by count_star desc limit 4;

# List queries creating temp tables

 select * from events_statements_current where CREATED_TMP_DISK_TABLES > 0 limit 1\G;
 
 select digest_text,sum_rows_sent,last_seen from events_statements_summary_by_digest 
 order by SUM_CREATED_TMP_DISK_TABLES desc limit 3\G;
 
# Top host/events

select * from events_statements_summary_by_host_by_event_name order by sum_rows_examined desc limit 1;

# Show enabled probes

 select * from setup_consumers;
 
 # Event  waits
 
 SELECT EVENT_NAME, COUNT_STAR, SUM_TIMER_WAIT, AVG_TIMER_WAIT  FROM events_waits_summary_global_by_event_name 
 ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;
 
 # Find out which hosts are running the queries
 
 select sql_text, processlist_host from events_statements_current join threads on events_statements_current.thread_id = threads.thread_id  whmit 4\G;