+-----------------------------------------+ | Next Output | +-----------------------------------------+ | Queries with Runtime in 95th Percentile | +-----------------------------------------+ 1 row in set (0.03 sec) ... 

Create an overall report of the 95th percentile queries and the top 10 queries with full table scans: 

mysql> CALL sys.statement_performance_analyzer(’snapshot’, NULL, NULL); Query OK, 0 rows affected (0.01 sec) mysql> SET @sys.statement_performance_analyzer.limit = 10; Query OK, 0 rows affected (0.00 sec) mysql> CALL +-----------------------------------------+ | Next Output | +-----------------------------------------+ | Queries with Runtime in 95th Percentile | +-----------------------------------------+ 1 row in set (0.01 sec) ... +-------------------------------------+ | Next Output | +-------------------------------------+ | Top 10 Queries with Full Table Scan | +-------------------------------------+ 1 row in set (0.09 sec) ... 

Use a custom view showing the top 10 queries sorted by total execution time, refreshing the view every minute using the watch  command in Linux: 

mysql> CREATE OR REPLACE VIEW mydb.my_statements AS SELECT sys.format_statement(DIGEST_TEXT) AS query, SCHEMA_NAME AS db, COUNT_STAR AS exec_count, sys.format_time(SUM_TIMER_WAIT) AS total_latency, sys.format_time(AVG_TIMER_WAIT) AS avg_latency, ROUND(IFNULL(SUM_ROWS_SENT / NULLIF(COUNT_STAR, 0), 0)) AS rows_sent_avg, ROUND(IFNULL(SUM_ROWS_EXAMINED / NULLIF(COUNT_STAR, 0), 0)) AS rows_examined_avg, ROUND(IFNULL(SUM_ROWS_AFFECTED / NULLIF(COUNT_STAR, 0), 0)) AS rows_affected_avg, DIGEST AS digest FROM performance_schema.events_statements_summary_by_digest ORDER BY SUM_TIMER_WAIT DESC; Query OK, 0 rows affected (0.10 sec) mysql> CALL sys.statement_performance_analyzer(’create_table’, ’mydb.digests_prev’, NULL); Query OK, 0 rows affected (0.10 sec) shell> watch -n 60 "mysql sys --table -e " > SET @sys.statement_performance_analyzer.view = ’mydb.my_statements’; > SET @sys.statement_performance_analyzer.limit = 10; > CALL statement_performance_analyzer(’snapshot’, NULL, NULL); > CALL statement_performance_analyzer(’delta’, ’mydb.digests_prev’, ’custom’); > CALL statement_performance_analyzer(’save’, ’mydb.digests_prev’, NULL); > "" Every 60.0s: mysql sys --table -e " ... Mon Dec 22 10:58:51 2014 +----------------------------------+ 

