MySQL Performance analysis System

The MySQL slow query log analysis is now provided by a variety of tools, the most original Mysqldumpslow, full-featured Mysqlsla and Percona pt-query-digest; These tools greatly improve the DBA's ability to analyze the performance of the database, Reduce the excessive guessing process;

What if you can perform a timed analysis of SQL and visualize the presentation?

The application of Query-digest-ui-master this UI plug-in, in conjunction with Percona pt-query-digest Tools, is simply to achieve a visual result, if the analysis of multiple servers, this performance is very difficult;

For MySQL slow query analysis and visualization we can take advantage of open source software anemometer,

Function:

Can be analyzed for slow queries in multiple db;

Provide explain function;

Generate a picture of the usage of an SQL statement and generate a permanent link;

Support Performance_schema Performance collection;

Sort according to time or other metrics:

Wait

The use of Percona pt-query-digest can be referred to this blog: http://weipengfei.blog.51cto.com/1511707/953075 (the version used this time is 2.1)

Focus on the following two parameters:

--review: The collected results are stored in the database, and the next time the analysis is repeated without recording;

--review-history: Stores collected historical data and carries out SQL trend analysis;

(can be created using--create-review-table and--create-review-history-table two parameters if there is no corresponding table)

Simple example:

Pt-query-digest--filter ' (($event->{full_scan} | | "") eq "yes") | | (($event->{full_join} | | "") eq "yes") ' Slow.log

Anemometer is required for LNMP or lamp support (PHP 5.3+);

Pt-query-digest Execution Process:

$ pt-query-digest--user=anemometer--password=supersecurepass \--review H=db.example.com,d=slow_query_log,t=global _query_review \--review-history h=host,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

For $hostname parameters We can use flexibly, can be viewed for different hosts;

$ mysql-h db.example.com < Install.sql

Configuration file Modification:

$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 ')    ));