Analyse slow-query-log using Anemometer

Prerequisite

  1. Enable mysql slow log – https://rtcamp.com/tutorials/mysql/slow-query-log/
  2. Peronca Toolkit – https://rtcamp.com/tutorials/mysql/percona-toolkit/

Installation

Download Anemometer & Extract it

cd /var/www/example.com/htdocs 
git clone https://github.com/box/Anemometer.git anemometer 
cd anemometer

Run mysql setup script and create a separate mysql user

mysql < install.sql 
mysql -e "grant ALL ON slow_query_log.* to 'anemometer'@'localhost' IDENTIFIED BY 'superSecurePass';"
mysql -e "grant SELECT ON *.* to 'anemometer'@'localhost' IDENTIFIED BY 'superSecurePass';"

process mysql slow log to populate anemometer database

Run following command. Make sure you update superSecurePass with your password.

pt-query-digest --user=anemometer --password=superSecurePass \
                  --review D=slow_query_log,t=global_query_review \
                  --review-history D=slow_query_log,t=global_query_review_history \
                  --no-report --limit=0% --filter=" \$event->{Bytes} = length(\$event->{arg}) and \$event->{hostname}=\"$HOSTNAME\""  /var/log/mysql/slow.log

If above command is successful, you will see data getting populated in global_query_review and global_query_review_history tables in mysql’s database slow_query_log

anemometer php config file

Copy sample config file:

cp conf/sample.config.inc.php conf/config.inc.php

Open config file conf/config.inc.php and look for user and password option. Default user in sample config file is root. Change that to anemometer and password to your superSecurePass.

Config block will look like:

$conf['datasources']['localhost'] = array(
        'host'  => 'localhost',
        'port'  => 3306,
        'db'    => 'slow_query_log',
        'user'  => 'anemometer',
        'password' => 'superSecurePass',
        'tables' => array(
                'global_query_review' => 'fact',
                'global_query_review_history' => 'dimension'
        ),
        'source_type' => 'slow_query_log'
);

You need to also update plugins section in same config file:

$conf['plugins'] = array(

               'visual_explain' => '/usr/bin/pt-visual-explain',
               'query_advisor' => '/usr/bin/pt-query-advisor',

#... other lines

                $conn['user'] = 'anemometer';
                $conn['password'] = 'superSecurePass';

                return $conn;
},

Update PHP’s timezone

You may run into errors when using Anemometer. Just copy timezone from /etc/timezone and update date.timezone value in `/etc/php5/fpm/php.ini.

Restart FPM using service php5-fpm restart

Automate slow.log processing

Open /etc/logrotate.d/percona-server-server-5.6 (if using percona-mysql 5.6) or /etc/logrotate.d/mysql. If both doesn’t exist figure out mysql slow log rotation file.

Then add slow.log processing command after postrotate but before endscript:

postrotate
pt-query-digest --user=anemometer --password=superSecurePass \
                  --review D=slow_query_log,t=global_query_review \
                  --review-history D=slow_query_log,t=global_query_review_history \
                  --no-report --limit=0% --filter=" \$event->{Bytes} = length(\$event->{arg}) and \$event->{hostname}=\"$HOSTNAME\"" /var/log/mysql/slow.log.1
endscript

Usage

Just open example.com/anemometer in browser!