Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to use Anemometer to analyze MySQL slow query records. Xiaobian thinks it is quite practical, so share it with you as a reference. I hope you can gain something after reading this article.

Database administrators generally use percona's toolkit tool to analyze MySQL slow query records, but it is not intuitive enough.

Here is a more intuitive tool to statistically analyze MySQL slow query records anemometer.

Percona's toolkit needs to be installed before use, and anemometer provides a web interface.

1. Toolkit installation

# cd /data/www/my.xuebuyuan.com

# git clone https://github.com/box/Anemometer.git anemometer

# cd anemometer

2. Create tables and user names

mysql

< install.sql 　　mysql -e "grant ALL ON slow_query_log.* to 'anemometer'@'localhost' IDENTIFIED BY 'my.xuebuyuan.com';" 　　mysql -e "grant SELECT ON *.* to 'anemometer'@'localhost' IDENTIFIED BY 'my.xuebuyuan.com';" 　　3. 分析MySQL慢日志 　　# 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\"" /data/log/mysql/slow.log

4. Configure anemometer

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

# vi conf/config.inc.php

$conf['datasources']['localhost'] = array(

'host' => 'localhost',

'port' => 3306,

'db' => 'slow_query_log',

'user' => 'anemometer',

'password' => 'my.ttlsa.com',

'tables' => array(

'global_query_review' => 'fact',

'global_query_review_history' => 'dimension'

),

'source_type' => 'slow_query_log'

);

$conf['plugins'] = array(

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

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

#... other lines

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

$conn['password'] = 'my.ttlsa.com';

return $conn;

},

5. automated processing

# vi /etc/logrotate.d/mysql

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\"" /data/log/mysql/slow.log.1

endscript

This will allow you to view slow queries via my.ttlsa.com/anemometer.

About "how to use Anemometer to analyze MySQL slow query records" this article is shared here, I hope the above content can be of some help to everyone, so that you can learn more knowledge, if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.