Visualization tools for pt-query-digest tables

PREVIOUS POST
NEXT POST

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:

PREVIOUS POST
NEXT POST

Comments

  1. 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.

  2. 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

  3. 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.

Leave a Reply

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