MYSQ-DBA should master the L management tool-pt-tool

Pt-query-digest

Pt-query-digest can parse queries from common MySQL logs, slow query logs, and binary logs, even from the tcpdump of show Processlist and MySQL protocols, if no files are specified, It reads data from the standard input stream (STDIN).

The simplest usage is as follows:

Pt-query-digest Slow.logs

The output information is roughly as follows:

The entire output is divided into three parts:

1. Overall overview (Overall)

This section is a general overview (similar to the one given by LoadRunner), which allows a preliminary evaluation of the current MySQL query performance, such as the maximum value (max), average (min), 95% distribution, median (median) of each indicator, Standard deviation (StdDev). These metrics have the execution time of the query (Exec times), the time that the lock takes, the number of rows that the MySQL executor needs to check (rows examine), the number of rows that are returned to the client (rows sent), and the size of the query.

2, the summary information of the query (profile)

This section makes a list of all "important" queries (usually slow queries):

Every query has a query ID, and this ID is calculated by hash. Pt-query-digest is based on the so-called fingerprint group by. For example, the fingerprint of the following two queries is the same as the select * FROM table1 where column1 =?, the Toolbox also has a related tool pt-fingerprint.

SELECT * FROM table1 where column1 = 2select * FROM table1 where column1 = 3

• Rank the "statement" in the entire analysis, generally the most common performance.
• Response The response time of the "statement" and the overall percentage.
• Calls the number of times the "statement" was executed.
• R/call The average response time for each execution.
• v/m response time difference average to ratio.

There is a line of output at the tail, showing the other 2 statistics that are relatively low and not worth displaying separately.

3. Detailed information

This section lists the details of each query in the profile table:

This includes information in the overall, the distribution of query response time, and the reason for the query "into the table."

Pt-query-digest also has a lot of complicated operation, not one of which is introduced here. For example: Query the slowest query in MySQL from processlist:

Pt-query-digest–processlist H=host1

From the Tcpdump analysis:

Tcpdump-s 65535-x-nn-q-tttt-i any-c $ port 3306 > mysql.tcp.txtpt-query-digest--type tcpdump mysql.tcp.txt

From one machine, slow log is saved on another machine. To be analyzed in detail later:

Pt-query-digest--review h=host2--no-report slow.log

You can also follow some filter conditions. See Official documentation: http://www.percona.com/doc/percona-toolkit/2.2/pt-query-digest.html

In addition to the combination of some third-party tools can also generate corresponding reports, you can refer to here: http://biancheng.dnbcw.info/mysql/433514.html

recommendation : When slow log is large, it is best to move the log files to other machines for analysis.

Pt-index-usage

This tool is primarily used to analyze the index usage of the query.

Pt-index-usage slow_query.log--h localhost--password 123456

Detailed usage help view and then compare with the official website is not to repeat the difference.

Note that the use of this tool requires MySQL must have a password, in addition to the runtime may not be able to find/var/lib/mysql/mysql.sock error, simple link from the/tmp/mysql.sock one on the line.

The key point to note is that Pt-index-usage can only parse the slow query log, so if you want to fully analyze the index usage of all queries will have to set the Slow_launch_time to 0, so use this tool with caution, it is best to use the line in the early morning analysis, In particular, the analysis of a large number of logs is very CPU-intensive.

As a whole, this tool is deprecated, and some other third-party tools, such as Mysqlidxchx, Userstat and Check-unused-keys, can be considered to implement a similar analysis. The online comparison recommended is Userstat, a Google contributed patch.

Oracle is able to save the execution plan to the Performance view, which may be more flexible to analyze, but I haven't found a similar approach in MySQL at the moment.

Pt-upgrade

This tool checks to see if the SQL running in the new version returns the same results as the old version, and the best scenario is when the data is migrated.

Pt-upgrade H=host1 H=host2 Slow.log

Pt-query-advisor

Static query analysis tools. Be able to parse the query log, parse the query pattern, and then give any queries that may have potential problems and give enough detailed advice. This tool seems to have been removed from the 2.2 version, possibly because the performance impact of the newer version is removed directly.

Summary : The above tools are best not to use directly online, should be used as an on-line auxiliary or offline analysis after the tool, you can also do performance testing with the use of the time.

SHOW profile

Show profile is a contribution to the MySQL community by Google's senior architect Jeremy Cole, which can be used for MySQL to execute statements using resources. The default is off and needs to be opened to execute the following statement:

Set profiling = 1; #这个命令只在本会话内起作用.

Performing a simple show profiles can see the execution time of all queries after opening profiling.

Execute show profile [TYPE] for query query_id to see the details of each of the performance metrics that MySQL performs for each step of a query:

Displays the details of the most recent query if you do not specify a for query. The type is optional and has the following options:

• All Show all performance information
• Block IO shows the number of blocks IO operations
• Context switches shows the number of contextual switches, whether active or passive
• CPU displays user CPU time, System CPU time
• IPC displays the number of messages sent and received
• MEMORY [temporarily not implemented]
• Page faults displays the number of pages errors
• Source shows the name and location of the function in the source code
• SWAPS shows the number of swaps

MySQL in the execution of the query statement there will be a lot of steps, here do not repeat, the use of the time on the Internet search on the line. In particular , sending data is a step that gives the impression that MySQL is time-consuming to send data to the client, which, in fact, involves the process of replicating data between various stores within MySQL, such as the drive's seek.

Summary : The previous slow query log analysis is more like a complete check of the entire MySQL query, and show profile is a single query statement analysis, usually when the current network problems should be combined with both. Use slow query analysis to navigate to specific queries, use show profile to navigate to specific issues, whether sending data is time consuming or system lock is time consuming ...

Performance_schema

The two libraries hold some of MySQL's performance and meta-data-related information, with Performance_schema being a new MySQL5.5, and many of the tools mentioned above actually take advantage of this library's information. For example, show profile all for Query 2: The information can also be obtained through the following query:

SELECT * from information_schema.profiling WHERE query_id = 2 ORDER by seq;

With Performance_schema, you can also do some more flexible statistics:

SET @query_id = 1; Select State,sum (DURATION) as Total_r,    ROUND (100*sum (DURATION)/(SELECT SUM (DURATION) from INFORMATION_SCHEMA. PROFILING    WHERE query_id = @query_id), 2) as Pct_r,    COUNT (*) as Calls,    SUM (DURATION)/count (*) as "R/call" From INFORMATION_SCHEMA. Profilingwhere query_id = @query_idGROUP by Stateorder by Total_r DESC;

This simple query, which can be found in high-performance MySQL third edition, can be used to count the time-consuming, time-consuming, API, and average time-consuming of each step of the show profile. A number of useful tools (SHOW status,show processlist ...) are also mentioned in the third edition of high-performance MySQL. This book is strongly recommended, including the use of the method.

MYSQ-DBA should master the L management tool-pt-tool