MySQL log file and analysis

1. Query log, slow query log, binary log comparison

Query log	general_log	All actions of the user are recorded, including additions, deletions, etc.	You can specify the output as a table
Slow query log	slow_log	As long as all action statements that exceed the defined time are recorded	You can specify the output as a table
Binary log	log_bin	Log all actions that might make changes	Mysqlbinlog View

2. Analysis of logs2.1 Storage of logs

During data manipulation, Mysqld writes the received statements to the query log file in the order in which they are received (note that it is not the order of execution). One article is like this:

# time:070927 8:08:52
# [email protected]: Root[root] @ [192.168.0.20]
# query_time:372 lock_time:136 rows_sent:152 rows_examined:263630
Select ID, name from manager where ID in (66,10135);

that way, when we look at the contents of the log, it takes a lot of time to get the eye. So how should it be broken?

Since the 5.1.6 version, there has been a wave of new features, such as query logs can be written to the database system in a dedicated table.

The MySQL command line can load many parameters at startup, which provides a log-specific parameter,--log-output, to specify how the log file will be output.

There are three optional values for the--log-output parameter:

• Table: Logs the log table to the database;
• File: Logs to the log file, the default value is file (at 5.1.6 to 5.1.20, the default value is table);
• None: Not logged.

(1) Can be table, file, NONE, or a combination of table and file (separated by commas), the default is table.

(2) If none is present in the combination, the other settings are invalidated and no log information is logged, regardless of whether logging is enabled or not.

(3) The scope is the global level, can be used for configuration files, belong to dynamic variables.

(4) Append--log-output Specifies the log output type when the MYSQLD process is enabled.

MySQL supports saving the slow query log to the Mysql.slow_log table:

2.2 Slow Query Analysis tool

If the slow query log output type has already specified file, in case of large log volume, we can use some analysis tools.

(1) Mysqldumpslow MySQL comes with the Slow query log Analysis tool Mysqldumpslow, which is easy to use and can be used with-help to see the specific usage. Key Features: Count the number of occurrences of slow SQL (count) to perform the longest time (time), the cumulative total duration (times), the time to wait for a lock, the total number of rows sent to the client (rows), the total number of rows scanned (rows), the user, and the SQL statement itself ( Abstract the format, for example, limit 1, 20 with limit n,n).

(2)Mysqlsla

hackmysql.com launch of a log analysis tool

Features are very powerful. The data report is very helpful for analyzing the reasons of slow query, including the frequency of execution, the amount of data, and the consumption of queries.

The format description is as follows:

Total queries (queries totals), the number of de-SQL (unique) Output report content sort (sorted by) The most significant slow SQL statistics, including average execution time, waiting lock time, total number of result rows, total number of rows scanned.

Count:sql the number of executions and the percentage of total slow log count.

Time: Execution times, including total time, average time, minimum, maximum time, and percentage of total slow SQL time.

95% of time: Removes the fastest and slowest sql, covering 95% of SQL execution times.

Lock time: When the lock is waiting.

95% of lock:95% Slow SQL waits for lock time.

Rows sent: The number of result rows, including the average, minimum, and maximum number.

Rows examined: Number of rows scanned.

Database: Which databases belong to.

Users: Which user IP accounts for the percentage of SQL performed by all users.

Query Abstract: The SQL statement after the abstraction.

Query sample:sql statement.

In addition to the above output, the official also provides a lot of custom parameters, is a rare good tool.

(3) 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

(4) pt-query-digest

You can parse queries from normal MySQL logs, slow query logs, and binary logs, even from the tcpdump of show Processlist and MySQL protocols, and if you don't specify a file, it reads data from the standard input stream (STDIN).

The simplest usage is as follows:

Pt-query-digest Slow.logs

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."

2.3BOX anemometer

BOX Anemometer is based on pt-query-digest to visualize MySQL slow query

MySQL log file and analysis