MySQL Optimization-to do what you want, you must first sharpen the tool (2)

The previous article briefly introduced the usage of the EXPLAIN statement. Today we will introduce the following:

Slow query log

• Enable slow query log
• Save slow query logs to the table
• Slow query log analysis

Percona Toolkit

• Install
• Pt-query-digest
• Pt-index-usage
• Pt-query-advisor

SHOW PROFILE

Performance_schema

 

Slow query log open slow query log

Slow query logs, as the name implies, are logs that record slow query execution.

Check whether the slow query log is Enabled:

show variables like ;

Open the slow query log. The configuration file my.cn for modifying MySQL is usually under the/etc directory. Add the following three lines to restart MySQL.

slow_query_log =-launch- = -query-log- = /usr/local/mysql/data/slow_query.log

Slow_launch_time can only be accurate to seconds. If you need more precision, you can use third-party tools such as pt-query-digest described later.

Note:The MySQL version I used here is 5.6. The configuration for enabling slow queries for MySQL of different versions is different. For example, some versions earlier than 5.6 are ong_query_time, long_query_time, and log-slow-queries. You can runShow variables like '% slow % ';Check the specific configuration of the current version.

You can also set global variables on the terminal to enable slow query logs:

set  @@global.slow_query_log = ON;

Save slow query logs to the table

MySQL supports saving slow query logs to the mysql. slow_log table. You can use @ global. log_output to set the default value to TABLE. Only one FILE and one TABLE can be used at the same time.

set @@global.log_output=;

We can use the following statement to simulate slow queries:

 ();

Slow query log analysis

1. You can use the mysqldumpslow tool that comes with MySQL. It is easy to use. You can follow-help to view the specific usage.

# ---- S r-t/-s t-g/slowquery. log #

2. You can export data to the mysql. slow_query table and analyze the data using SQL statements.

3. Use third-party tools, which will be described below.

 

Percona Toolkit

Percona-toolkit is a set of advanced command line tools used to execute various complicated and troublesome mysql and system tasks by hand. These tasks include:

• Check master and slave Data Consistency
• Effectively archive records
• Searching duplicate Indexes
• Summarize Server Information
• Analyze queries from logs and tcpdump
• Collect important system information when a system problem occurs

Install

Install percona-toolkitvery easily. Download The .tar.gz package from the official website:

 percona.com/get/percona-toolkit. -zxvf percona-toolkit-...gz

Then execute the following commands in sequence:

Installed in the/usr/local/bin directory by default. Run man percona-toolkit to check which tools are installed.

The following error may occur when running the tool:

This is because the corresponding package is missing. The. pm package is actually a perl package. Run the following command to install it:

  -y -Time-HiRes

If the "Error Downloading Packages" Error occurs during the installation process, try yum clean all before installing it. Using other tools in its Percona Toolkit may also encounter similar problems. Follow the prompts to install the corresponding perl package.

The entire Toolkit of Percona Toolkit provides a lot of practical tools. For specific usage instructions, see the official documentation.

The following are some useful tools.

Pt-query-digest

Pt-query-digest can be used to analyze and query common MySQL logs, slow query logs, and binary logs, or even from the tcpdump of show processlist and MySQL protocol. If no file is 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 summary (Overall)

This section is a rough summary information (similar to the summary information provided by loadrunner), through which you can make a preliminary evaluation of the current MySQL query performance, for example, the maximum value (max), average value (min), 95% distribution value, median, and stddev of each indicator ). These indicators include the query execution time (Exec time), Lock time (Lock time), and the number of Rows to be checked by the MySQL Executor (Rows examine ), the number of Rows (Rows sent) returned to the client, the size of the query.

2. query summary information (Profile)

This section provides a list of all "important" Queries (usually slow queries:

Each Query has a Query ID, which is calculated by Hash. Pt-query-digest uses the so-called Fingerprint to group. For example, the Fingerprint of the following two queries is the same as select * from table1 where column1 = ?, The toolkit also has a related tool pt-fingerprint.

 * from table1 where column1 =  * from table1 where column1 = 

• Rank ranks the "statement" in the entire analysis, which is generally the most common performance.
• Response time and overall proportion of the Response time statement.
• The number of times the "statement" is executed.
• The average response time of each R/Call execution.
• V/M average ratio of response time difference.

There is a row of output at the end, showing the other two statistics that are relatively low and not worth displaying separately.

3. Details

This section lists the details of each query in the Profile Table:

Including the information in Overall, the distribution of the query response time, and the reason for this query to be "listed.

Pt-query-digest has many complicated operations, which will not be described here. For example, query the slowest query in a MySQL instance from PROCESSLIST:

pt-query-digest –processlist h=host1

Analyze from tcpdump:

tcpdump -s  -x -nn -q -tttt -i any -c  port  >-query-digest --type tcpdump mysql.tcp.txt

From one machine, the slow log is saved to another machine for further analysis:

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

You can also follow some filter conditions. See the official document for details: http://www.percona.com/doc/percona-toolkit/2.2/pt-query-digest.html

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

Suggestions: When the slow log is large, it is best to move the log file to another machine for analysis.

 

Pt-index-usage

This tool is mainly used to analyze the query index usage.

pt-index-usage slow_query.log --h localhost --password 

For more information, see "help.

Note:To use this tool, you must have a password for MySQL. In addition, you may not be able to find/var/lib/mysql during runtime. sock error, simple from/tmp/mysql. just link one sock.

Note that pt-index-usage can only analyze slow query logs. to fully analyze the index usage of all queries, set slow_launch_time to 0, therefore, please use this tool with caution. If you use it online, you 'd better analyze it in the early morning, especially when analyzing a large number of logs, which consumes a lot of CPU.

In general, this tool is not recommended. To achieve similar analysis, you can consider other third-party tools, such as mysq1_xchx, userstat, and check-unused-keys. On the Internet, we recommend userstat, a patch contributed by Google.

Oracle can save the execution plan to the performance view. This analysis may be more flexible, but I have not found a similar approach in MySQL.

 

Pt-upgrade

This tool is used to check whether the SQL statements run in the new version are the same as those in the old version and return the same results. The best application scenario is data migration.

ptupgrade hhost1 hhost2 slow.

 

Pt-query-advisor

Static query analysis tool. It can parse query logs, analyze query modes, and then provide all queries that may have potential problems, and give enough detailed suggestions. This tool seems to have been removed from Version 2.2, probably because it has a significant impact on the performance of new versions.

Summary: These tools should not be directly used online. They should be used as online auxiliary tools or offline analysis tools after failures. They can also be used together during performance testing.

 

SHOW PROFILE

Show profile is contributed by Jeremy Cole, Google's senior architect, to the MySQL Community. It can be used for resources used when MySQL executes statements. It is disabled by default. You need to open and execute the following statement:

Set profiling =; # This command only works in this session.

Run show profiles to view the execution time of all queries after profiling is enabled.

Run show profile [TYPE] for query Query_ID to view detailed information about the performance indicators of MySQL in each QUERY step:

If for query is not specified, the details of the last QUERY are displayed. TYPE is optional and has the following options:

• ALL show ALL performance information
• Block io displays the number of block io operations
• Context switches: displays the number of context switches, whether active or passive.
• CPU displays the user's CPU time and system CPU time
• IPC displays the number of messages sent and received
• MEMORY [not implemented yet]
• Page faults display PAGE error count
• SOURCE: displays the function name and position in the SOURCE code.
• Number of SWAPS displayed

MySQL has many steps when executing the query statement. I will not describe them here. I can search them online. YesNoteIt is the Sending data step, which makes it time for MySQL to send data to the client. In fact, it is not the case. This step includes the process of copying data between various storage in MySQL, such as hard disk seek.

Summary: The previous slow query log analysis is more like a comprehensive check of the entire MySQL query, while show profile is an analysis of a single query statement, generally, when problems occur on the current network, they should be combined. Locate the specific query by using the slow query analysis, and locate the specific problem by using show profile, whether the Sending data is time-consuming or the System lock is time-consuming...

 

Performance_schema

These two databases store some MySQL performance and metadata-related information. performance_schema is newly added only when mysql5.5. many of the tools mentioned above actually use the information of this database. FOR example, the show profile all for query 2: Information can also be obtained through the following QUERY:

   information_schema.profiling  query_id     seq;

You can also make more flexible statistics using performance_schema:

    STATE,(DURATION) ((DURATION)( (DURATION)  QUERY_ID  ),) () (DURATION)()  "R QUERY_ID     Total_R ;

This simple query can be found in high-performance MySQL 3, which can count the time consumption, time consumption ratio, number of API calls, and average time consumption of each step of show profile. Many useful tools (show status, show processlist...), including usage methods, are also mentioned in the High-Performance MySQL Third Edition. This book is strongly recommended.

 

OK. Today's introduction is here. The main purpose of these two articles is to know where to start when you discover the problem, and hope to bring some practical value to everyone. The next article will introduce some basic principles of MySQL statement execution!