About MySQL General query log and slow query log analysis

Logs in MySQL include: error log, binary log, General query log, slow query log, and so on. Here is the main introduction of the more commonly used two features: General query log and slow query log.

1) General query log: Records established client connections and executed statements.

2) Slow query log: Log all queries that have been executed for more than long_query_time seconds or queries that do not use an index

(1) General query log

When learning a generic log query, you need to know the common commands in two databases:

1) showvariables like '%version% ';

As follows:

The above command shows what is related to the version number in the current database.

1) showvariables like '%general% ';

You can see whether the current Universal log query is turned on, or off if the value of General_log is on, or off (by default).

1) showvariables like '%log_output% ';

View the format of the current slow query log output, either file (Hostname.log stored in a data file in the database) or table (Mysql.general_log stored in the database)

Question: How do I turn on the MySQL universal query log, and how do I set the common log output format to output?

turn on Universal log query: set global general_log=on;

turn off common log query: set Globalgeneral_log=off;

set general log output to table: set globallog_output= ' table ';

set general log output to file mode: set globallog_output= ' file ';

Set general log Output to table and file mode: Set global log_output= ' file,table ';

(Note: The above command is only valid for the current, when the MySQL restart fails, if you want to permanently take effect, you need to configure MY.CNF)

The log output is as follows:

The data recorded in the Mysql.general_log table is as follows:

The format for logging to. Log in local is as follows:

The configuration of the My.cnf file is as follows:

General_log=1 #为1表示开启通用日志查询 with a value of 0 to turn off common log queries

log_output=file,table# setting the output format of the common log for files and tables

(2) Slow query log

MySQL's slow query log is a log record provided by MySQL, used to record in MySQL response time exceeding the threshold of the statement, specifically, the run time exceeds the Long_query_time value of SQL, will be recorded in the Slow query log (log can be written to a file or database table, If the performance requirements are high, it is recommended to write the file). By default, the MySQL database does not turn on the slow query log, the default value of Long_query_time is 10 (that is, 10 seconds, usually set to 1 seconds), that is, the statement running more than 10 seconds is a slow query statement.

In general, slow queries occur in large tables (for example, a table has millions of of the data), and the fields of the query criteria are not indexed, at this point, the field to match the query criteria for a full table scan, time-consuming check long_query_time,

is a slow query statement.

Question: How do I see how the current slow query log is turned on?

In MySQL, enter the command:

Showvariables like '%quer% ';

Master the following several parameters:

(1) The value of Slow_query_log is on to open the slow query log, off is to turn off the slow query log.

(2) The value of Slow_query_log_file is a slow query log to the file (note: The default name is hostname. Log, the slow query log is written to the specified file, you need to specify the output log format of the slow query as a file, the related command is: show variables like ' %log_output% '; To see the format of the output).

(3) Long_query_time Specifies the threshold for a slow query, which is a slow query statement if the time to execute the statement exceeds the threshold, and the default value is 10 seconds.

(4) Log_queries_not_using_indexes If the value is set to ON, all queries that do not utilize the index are logged (note: If you just set log_queries_not_using_indexes to ON, Slow_ Query_log is set to OFF, this setting also does not take effect, that is, the setting takes effect if the value of Slow_query_log is set to ON), which is normally turned on temporarily when performance is tuned.

Question: Does the output log format for MySQL slow query be a file or a table, or both?

By command: Show variables like '%log_output% ';

The value of Log_output can be viewed in the format of the output, the value of which is table. Of course, we can also set the output format as text, or simultaneously record both the text and the database table, setting the command as follows:

#慢查询日志输出到表中 (i.e. Mysql.slow_log)

Set globallog_output= ' TABLE ';

#慢查询日志仅输出到文本中 (that is, the file specified by Slow_query_log_file)

Setglobal log_output= ' FILE ';

#慢查询日志同时输出到文本和表中

Setglobal log_output= ' file,table ';

Data format analysis for data in a table with a slow query log:

The log records for slow queries are in the Myql.slow_log table, in the following format:

Slow query logging to the Hostname.log file, in the following format:

As you can see, whether it is a table or a file, it is specifically recorded: That statement causes slow query (Sql_text), the query time (query_time) of the slow query statement, the lock table time (Lock_time), and the number of scanned rows (rows_examined) and other information.

Question: How do I query the number of statements for the current slow query?

There is a variable in MySQL that specifically records the number of current slow query statements:

Input command: Show global status like '%slow% ';

(Note: All of the above commands, if the parameters are set through the MySQL shell, if you restart MySQL, all the set parameters will be invalidated, if you want to take effect permanently, you need to write the configuration parameters in the My.cnf file).

Supplemental knowledge Points: How do I use the Slow query log analysis tool from MySQL to Mysqldumpslow analysis logs?

Perlmysqldumpslow–s c–t Slow-query.log

The specific parameters are set as follows:

-S means the sort by how, C, T, L, R are in accordance with the number of records, time, query time, the number of records returned to sort, AC, at, AL, AR, indicating the corresponding flashbacks;

-T is the meaning of top, followed by the data to return the first number of bars;

-G can write regular expression matching, case insensitive.

The parameters in the above have the following meanings:

The count:414 statement appeared 414 times;

Time=3.51s (1454) The maximum time to execute is 3.51s, the total time spent 1454s;

lock=0.0s (0) Wait for the maximum time to lock 0s, the cumulative waiting lock time is 0s;

rows=2194.9 (9097604) The maximum number of rows sent to the client is 2194.9, and the cumulative function sent to the client is 90976404

http://blog.csdn.net/a600423444/article/details/6854289

(Note: The Mysqldumpslow script is written in Perl, the use of specific mysqldumpslow later)

Question: In the course of learning, how to know the slow query is valid?

Very simply, we can manually generate a slow query statement, for example, if our slow query Log_query_time value is set to 1, then we can execute the following statement:

Selectsleep (1);

The statement is a slow query and can then be used to see if there is a statement in the corresponding log output file or table.

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced. 75268151

About MySQL General query log and slow query log analysis