MySQL Optimization slow log analysis (anemometer+pt-query-digest)

Introduced
Use Pt-query-digest to collect slow query logs. Store the data in two tables: Global_query_review and Global_query_review_history. The data collected by Pt-query-digest is then presented in Web Form using Anemometer to facilitate query analysis.

1. Preparation conditions:
A.LNMP Platform
B.mysql Turn on Slow query
Slow_query_log=on #开启数据库的慢日志
long_query_time=0.1 #时间超过0.1s in the SQL log log
Log-slow-queries=/usr/local/mysql/data/mysql_slow.log #慢日志存放路径

2. Install Percona Toolkit
Download

wget https://www.percona.com/downloads/percona-toolkit/2.2.16/tarball/percona-toolkit-2.2.16.tar.gz

Tar xzvf percona-toolkit-2.2.16.tar.gz
CD percona-toolkit-2.2.16
Perl makefile.pl
Make
Make install

3. Installation Configuration Anemometer
A. Download: Https://github.com/box/Anemometer
Unzip the anemometer package, rename it to Anemometer, and move to/usr/local/nginx/html/anemometer (nginx default path)
Execute the setup script to create the user:
[[Email Protected]/anemometer]mysql-uroot-pxxx < Install.sql
[[Email protected]/anemometer]mysql-uroot-pxxx
MySQL > Grant all on slow_query_log.* to ' anemometer ' @ '% ' identified by ' test ';
MySQL > Grant SELECT on * * to ' anemometer ' @ '% ';
MySQL > Grant all on slow_query_log.* to ' anemometer ' @ ' localhost ';
MySQL > Grant SELECT on * * to ' anemometer ' @ ' localhost ';

B. Store the slow query log in the database by Pt-query-digest analysis:

Pt-query-digest--user=anemometer--password=test--review H=154.1.1.36,d=slow_query_log,t=global_query_review-- History h=154.1.1.36,d=slow_query_log,t=global_query_review_history--no-report--limit=0%--filter= "\ $event->{ Bytes} = Length (\ $event->{arg}) and \ $event->{hostname}=\ "$HOSTNAME \" "/usr/local/mysql/data/web00-slow.log

C. Modify the configuration information for the visual interface:
Cd/usr/local/nginx/html/anemometer/conf
CP sample.config.inc.php config.inc.php
VI config.inc.php

$conf [' datasources '] [' 154.1.1.36 '] = array (         ' host ')   =>  ' 154.1.1.36 ',         ' Port '   =>  3306,         ' db '     =>  ' Slow_query_ Log ',         ' user '   =>  ' anemometer ',          ' password '  =>  ' test ',          ' Tables '  => array (                  ' Global_query_review '  =>  ' fact ',                  ' Global_query_review_history '  =>   ' dimension '         ),          ' Source_type '  =>&nbsP; ' 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 '] = ' test '; return $conn;},

Note: Update PHP timezone:/usr/local/php/etc/php.ini timezone =asia/shanghai

4. Final local access via IP:
Http://154.1.1.36/anemometer

650) this.width=650; "title=" 111.png "style=" Float:none; "alt=" wkiol1asaoigpmqzaahi7riwlkg768.png "src=" http:/ S1.51cto.com/wyfs02/m02/7a/8e/wkiol1asaoigpmqzaahi7riwlkg768.png "/>

B. Click on a specific checksum value to enter the page as follows:

650) this.width=650; "title=" 222.png "alt=" Wkiol1asaokbzzgkaaedzu8qm3o276.png "src=" http://s3.51cto.com/wyfs02/M02 /7a/8e/wkiol1asaokbzzgkaaedzu8qm3o276.png "/>

Add more pictures later ...

This article is from the "59090939" blog, please be sure to keep this source http://59090939.blog.51cto.com/6338052/1740039

MySQL Optimization slow log analysis (anemometer+pt-query-digest)