October 1, 2014

Visualization tools for pt-query-digest tables

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:

About Roman Vynar

Roman is a Remote DBA in Percona. His duties include handling remote DBA ops, developing and improving of monitoring tools. He also maintains Percona Monitoring Plugins project.

Comments

  1. Rob Smith says:

    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!

  2. Roman Vynar says:

    Hi Rob,

    Thank you for getting back.
    Ok, I have found what is the problem.

    By default, config.php.sample lists:
    ————————————————–

    $reviewhost = array(
    // Replace hostname and database in this setting
    // use host=hostname:port if not the default port
    ‘dsn’ => ‘mysql:host=hostname;dbname=percona’,

    $explainhosts = array(
    ‘label1′ => array(
    ‘dsn’ => ‘mysql:host=hostname1:3306′,
    ‘user’ => ‘user’,
    ‘password’ => ‘password’,

    ————————————————–

    1. If you set reviewhost dsn to ‘mysql:host=localhost:3306;dbname=percona’ Query Digest UI will not work at all however the comment above says the port could be specified.

    2. If you set explainhost dsn to ‘mysql:host=localhost:3306′ according to the sample the explain functionality will break with the error:
    PHP Notice: Undefined property: Database_pdo::$db in /home/html/QDU/libs/Database/Database/pdo.php on line 166
    PHP Notice: Undefined variable: errorinfo in /home/html/QDU/libs/Database/Database/pdo.php on line 177
    PHP Fatal error: Can’t connect to the database server. (test@:/)
    SQLSTATE[HY000] [2005] Unknown MySQL server host ‘localhost:3306′ (0) [#2005] in /home/html/QDU/libs/Database/Database.php on line 411

    If you set both dsns without specifying the port number everything will work properly.

    Therefore, I think the port definition should be fixed or cleared in the config.php.sample.

  3. Rob Smith says:

    Whoops! The example is fixed.

  4. Roman Vynar says:

    Thank you Rob!
    I have updated this article.

  5. Laurent says:

    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

  6. Roman Vynar says:

    Laurent,

    I also see many PHP notices and warnings, e.g.
    PHP Warning: stream_get_contents() expects parameter 1 to be resource, null given in /var/www/html/anemometer/lib/AnemometerModel.php on line 323
    PHP Notice: Undefined index: ref in /var/www/html/anemometer/lib/QueryExplain.php on line 237

    Probably, it is worth to file the bug to fix all of them.
    But anyway it works for me.

    In order to have “show query” working you need to setup $conf['plugins']['explain'] settings in the config file.
    At least:
    $conn['user'] = ‘test';
    $conn['password'] = ‘pass';
    and make sure ‘hostname_max’ is properly populated in query_review_history table.

Speak Your Mind

*