This question was asked at support by a customer to solve a difficult issue.
How to identify a sporadic burst of queries coming from one of the hosts accessing the database ?
If there are hundreds of hosts, it can be challenging, especially if the queries are fast. No chance for them to get logged in the famous slow query log !
Here is the solution using the performance_schema in MySQL 5.6 :
SELECT host, SUM(essbben.count_star) AS total_statements, format_time(SUM(essbben.sum_timer_wait)) AS total_latency, format_time(SUM(essbben.sum_timer_wait) / SUM(count_star)) AS avg_latency FROM performance_schema.events_statements_summary_by_host_by_event_name essbben GROUP BY host ORDER BY SUM(sum_timer_wait) DESC;
Here is the result[Read more...]