This question was asked at support by a customer to solve a difficult issue.<\/p>\n
How to identify a sporadic burst of queries coming from one of the hosts accessing the database ?<\/strong><\/p>\n 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 !<\/p>\n Here is the solution using the performance_schema in MySQL 5.6 :<\/p>\n Here is the result :<\/p>\n Note that if you use the thread pool plugin, you need to upgrade to 5.6.15 or later because of this bug :\u00a0Bug 17049691 : PROCESSLIST_USER AND PROCESSLIST_HOST ARE ALWAYS NULL WITH THREAD_POOL.<\/p>\n 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 :<\/p>\n I created a pull request<\/a> for mysql-sys, so that host based P_S tables can be accessed using sys views and possibly from MySQL Workbench 6.1.<\/p>\n","protected":false},"excerpt":{"rendered":" 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 … Continue reading SELECT\r\nhost,\r\nSUM(essbben.count_star) AS total_statements,\r\nformat_time(SUM(essbben.sum_timer_wait)) AS total_latency,\r\nformat_time(SUM(essbben.sum_timer_wait) \/ SUM(count_star))\r\nAS avg_latency\r\nFROM\r\nperformance_schema.events_statements_summary_by_host_by_event_name essbben\r\nGROUP BY\r\nhost\r\nORDER BY\r\nSUM(sum_timer_wait) DESC;<\/pre>\n
+---------------+------------------+---------------+-------------+\r\n| host | total_statements | total_latency | avg_latency |\r\n+---------------+------------------+---------------+-------------+\r\n| localhost | 30791 | 1.22h | 143.19 ms | \r\n| host1 | 6576077 | 1.14h | 622.17 us |\r\n| host2 | 53531 | 00:28:56.92 | 324.46 us |\r\n...<\/pre>\n
create table log_host\r\n(snapshot_time datetime, host varchar(60), \u00a0total_statements bigint, total_latency bigint) engine=InnoDB;\r\n-- script to run to archive the snapshot\r\nset @now = (select NOW());\r\ninsert into log_host(snapshot_time, host, total_statements, total_latency) SELECT @now, host,\r\n\u00a0 \u00a0SUM(essbben.count_star) AS total_statements,\r\n\u00a0 \u00a0SUM(essbben.sum_timer_wait) AS total_latency\r\n\u00a0 \u00a0FROM\r\n\u00a0 \u00a0performance_schema.events_statements_summary_by_host_by_event_name essbben\r\n\u00a0 \u00a0WHERE 1 = 1\r\n\u00a0 \u00a0GROUP BY host\r\n\u00a0 \u00a0ORDER BY SUM(sum_timer_wait) DESC;<\/pre>\n