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 :
|
1 2 3 4 5 6 7 8 9 10 11 12 |
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 :
|
1 2 3 4 5 6 7 |
+---------------+------------------+---------------+-------------+ | host | total_statements | total_latency | avg_latency | +---------------+------------------+---------------+-------------+ | localhost | 30791 | 1.22h | 143.19 ms | | host1 | 6576077 | 1.14h | 622.17 us | | host2 | 53531 | 00:28:56.92 | 324.46 us | ... |
Note that if you use the thread pool plugin, you need to upgrade to 5.6.15 or later because of this bug : Bug 17049691 : PROCESSLIST_USER AND PROCESSLIST_HOST ARE ALWAYS NULL WITH THREAD_POOL.
If you want to determine when the burst occurs, you can create an event or a cron job to load the results in a table like this :
|
1 2 3 4 5 6 7 8 9 10 11 12 |
create table log_host (snapshot_time datetime, host varchar(60), total_statements bigint, total_latency bigint) engine=InnoDB; -- script to run to archive the snapshot set @now = (select NOW()); insert into log_host(snapshot_time, host, total_statements, total_latency) SELECT @now, host, SUM(essbben.count_star) AS total_statements, SUM(essbben.sum_timer_wait) AS total_latency FROM performance_schema.events_statements_summary_by_host_by_event_name essbben WHERE 1 = 1 GROUP BY host ORDER BY SUM(sum_timer_wait) DESC; |
I created a pull request for mysql-sys, so that host based P_S tables can be accessed using sys views and possibly from MySQL Workbench 6.1.