When you process MySQL slow query logs using pt-query-digest you can store samples of each query into query_review table and historical values for review trend analysis into query_review_history table. But it could be difficult to easily browse those tables without a good GUI tool.

For the visual browsing of tables created by pt-query-digest you may want to use some kind of web tools besides phpMyAdmin 😉

Query Digest UI

This is a advanced, but easy to install, ui for pt-query-digest or mk-query-digest.

Main features:
* Dynamic filtering and searching of queries
* Colorized and normalized SQL syntax
* Explain the query dynamically
* Integrated pt-query-advisor support
* Detailed query historic stats, support for log_slow_verbosity.

Actually, this is a very simple and straightforward tool to browse slow queries. The web interface is AJAX-based. Please refer to the screenshots below to see what columns you can filter the report on. Personally, I find this tool useful in case you want to easily find a certain query by id, have it syntax-highlighted or find queries that have first been seen since the specified date or date range.
Also you can explain queries, see their stats and post reviews.

Box Anemometer

Anemometer is a tool for visualizing collected data from the MySQL Slow Query Log. The name comes from the instrument in a weather station that measures wind speed. SQL queries are like the wind — ephemeral and hard to get a hold of. Anemometer helps you get a handle on your queries; it makes it easier to figure out what to optimize and how to track performance over time.

Anemometer relies on the Percona Toolkit to do the slow query log collection. Specifically you can run pt-query-digest. To parse your slow logs and insert them into a database for reporting.

Personally, I find this tool as an advanced instrument that could be useful for:

* browsing queries with optional filtering on first seen date;
* custom reports, choosing which columns to show;
* filtering queries by hosts;
* filtering reports by different query conditions;
* graphing reports;
* explaining queries;
* searching and displaying samples;
* displaying table status and CREATE statements;
* reviewing and commenting on queries.

Despite its lack of ability to save predefined reports in the web interface for easy access, it does have a Permalink feature and API for relative date ranges. The web interface is pretty flexible as built on JQuery UI.

Take a look into the screenshots:

6 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Rob Smith

Hi Roman,

Sorry to hear that the explain functionality is broken for you. Is there any output in the apache error logs? If not, you can browse to the explain url directly ( http://path/to/QDUI/explain.php?checksum=SetMeHere&explainDb=SetMeHereToo

With valid values from checksum and explainDb, and it should show what’s wrong. If you can let me know what’s up, I’ll get it fixed up for you!

Rob Smith

Whoops! The example is fixed.

Laurent

Hi,

Thanks for this article, I got Box Anemometer installed on a dev server to check how it works and how it looks like.
But the “Show query” (when clicking on a checksum) seems to be not working properly as I’m having a lot of errors about :
Undefined index: id in /var/www/box-anemometer/lib/QueryExplain.php on line 226

Warning: stream_get_contents() expects parameter 1 to be resource, null given in /var/www/box-anemometer/lib/AnemometerModel.php on line 323

Can’t succeed yet to find the root cause of the problem, does any of you having the same problem please or do I have to file a bug entry on the Box Anemometer webpage ?

Thanks for your answer.
Regards,

Laurent