Phân tích mysql slow log với pt-query-digest

pt-query-digest là tool phân tích Mysql query từ slow log, general log và binary log file. Nó có thể phân tích query từ mysql processlist và tcpdump mysql data.  Tools này là một phần của bộ percona toolkit.

Slow query logs

• Enable slow query log:

slow_query_log = 1

slow_query_log_file = /var/log/mysql/mysql-slow.log

long_query_time = 2

log_queries_not_using_indexes

• enable the slow query log without a mysql-server restart:

set global slow_query_log = 1;

set global slow_query_log_file = '/var/log/mysql/mysql-slow.log';

set global long_query_time = 2;

set global log_queries_not_using_indexes=1;

• pt-query-digest with mysql slow.log

root@krishna:~# pt-query-digest /var/log/mysql/mysql-slow.log

• pt-query-digest with processlist

root@krishna:~# pt-query-digest --processlist h=host,user=user,password=pass --output=slow.log

• pt-query digest with tcpdump:

root@krishna:~# tcpdump -s 65535 -x -nn -q -tttt -i any -c 50000 port 3306 > mysqltcp.txt

root@krishna:~# pt-query-digest --type tcpdump mysqltcp.txt

• Graph

from: https://www.olindata.com/en/blog/2014/07/anemometer-mysql-slow-query-monitor

Anemometer is a MySQL slow query monitoring tool. It's used to analyze/visualize slow query log, collected from MySQL instance to identify the problematic queries. Also, makes it easier to figure out what to optimize and how to track performance over time.

Required:

• MySQL database to store query
• pt-query-digest from Percona
• slow query log from MySQL server
• Web server with php

Setup:

Configure, webserver with php, get aneommeter code from github and place into the document root of the webserver.

$ sudo git clone git://github.com/box/Anemometer.git anemometer 

OR, the below link can be used to get anemometer

$ sudo git clone git://github.com/box/Anemometer.git anemometer 

Load anemometer sql file into MySQL Server

 and provide grants to an user.

$ mysql < install.sql
$ mysql> grant ALL ON slow_query_log.* to 'anemometer'@'%' IDENTIFIED BY 'pass';
Query OK, 0 rows affected (0.01 sec)
mysql> flush logs;
Query OK, 0 rows affected (0.00 sec)

Populate data in the db:

Run pt-query-digest on the slow query log as shown below. Get pt-query-digest from here.

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

Data Viewing:

Go to the document root of the web server, change directory to conf and copy the sample.conf as below.

$ cd anemometer/conf
$ cp sample.config.inc.php config.inc.php 

Edit the config file and make the necessary change:

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

Now, you should be able to navigate to your browser.

Conclusion:

Anemometer is a good tool for visual slow query moniter, along with graph. The worst performing query, can be pointed out very easily.