Performance_schema success stories : host summary tables

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 :

Here is the result :

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 :

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.

This entry was posted in performance_schema, success stories, tip, Uncategorized. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *