Simple analysis of MySQL general query log and slow query log

This article brings you a simple analysis of the MySQL general query log and slow query log. It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.

The logs in MySQL include: error log, binary log, general query log, slow query log, etc. Here we mainly introduce two commonly used functions: general query log and slow query log.

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

2) Slow query log: record all queries whose execution time exceeds longquerytime seconds or queries that do not use indexes

(1) General query log

Learning general log When querying, you need to know the common commands in the two databases:

1) show variables like '%general%';

You can check whether the current general log query is enabled, and if the general_log A value of ON means it is turned on, and a value of OFF means it is turned off (it is turned off by default).

1) show variables like '%log_output%';

View the current slow query log output format, which can be FILE (hostname.log stored in the data file of the database), It can also be TABLE (mysql.general_log stored in the database)

Question: How to open the MySQL general query log, and how to set the general log output format to be output?

Enable general log query: set global general_log=on;

Turn off general log query: set global general_log=off;

Set general log output to table mode: set global log_output='TABLE';

Set the general log output to the file mode: set global log_output='FILE';

Set the general log output to the table and file mode: set global log_output='FILE ,TABLE';

(Note: The above command only takes effect currently and will fail when MySQL is restarted. If you want it to take effect permanently, you need to configure my.cnf)

The configuration of my.cnf file is as follows:

general_log=1 #A value of 1 means enabling general log query, a value of 0 means turning off general log query

log_output=FILE,TABLE#Set the output format of the general log to files and tables

(2) Slow query log

MySQL's slow query log is a log record provided by MySQL. It is used to record statements whose response time exceeds the threshold in MySQL. Specifically, it refers to the running time exceeding the long_query_time value. SQL will be recorded in the slow query log (the log can be written to a file or database table. If the performance requirements are high, it is recommended to write a file). By default, the MySQL database does not enable slow query logs. The default value of long_query_time is 10 (that is, 10 seconds, usually set to 1 second), that is, statements that run for more than 10 seconds are slow query statements.

Generally speaking, slow queries occur in large tables (for example: a table has millions of data), and the fields of the query conditions are not indexed. At this time, the fields that need to match the query conditions will be Full table scan, long_query_time is time-consuming,

is a slow query statement.

Question: How to check whether the current slow query log is enabled?

Enter the command in MySQL:

show variables like '%quer%';

Mainly master the following parameters:

(1) The value of slow_query_log is ON to enable the slow query log, and OFF to close the slow query log.

(2) The value of slow_query_log_file is the recorded slow query log to the file (note: the default name is hostname.log. Whether the slow query log is written to the specified file, you need to specify the output log format of the slow query is a file, the relevant command is: show variables like '%log_output%'; to view the output format).

(3) long_query_time specifies the threshold of slow query, that is, if the time to execute the statement exceeds the threshold, it is a slow query statement. The default value is 10 seconds.

(4) log_queries_not_using_indexes If the value is set to ON, all queries that do not use indexes will be recorded (Note: If you just set log_queries_not_using_indexes to ON and slow_query_log is set to OFF, this setting will not To take effect, that is, the premise for this setting to take effect is that the value of slow_query_log is set to ON). It is generally turned on temporarily during performance tuning.

Question: Set the output log format of MySQL slow query to file or table, or both?

Use the command: show variables like ‘%log_output%’;

You can check the output format through the value of log_output. The above value is TABLE. Of course, we can also set the output format to text, or record text and database tables at the same time. The setting command is as follows:

Slow query log output to the table (i.e. mysql.slow_log)

set globallog_output='TABLE';

The slow query log is only output to text (ie: the file specified by slow_query_log_file)

setglobal log_output ='FILE';

Here I recommend an architecture learning and exchange group to everyone. Communication and learning group number: 478030634. It will share some videos recorded by senior architects: Spring, MyBatis, Netty source code analysis, principles of high concurrency, high performance, distributed, microservice architecture, JVM performance optimization, distributed architecture, etc. These have become a necessary knowledge system for architects. You can also receive free learning resources and benefit a lot so far

The slow query log is output to text and tables at the same time

setglobal log_output='FILE,TABLE';

About the data in the table of the slow query log Analysis of the data format in the text:

The slow query log record in the myql.slow_log table has the following format:

Slow query The log is recorded to the hostname.log file in the following format:

You can see that whether it is a table or a file, it is recorded specifically: Yes That statement caused the slow query (sql_text), the query time (query_time), the table lock time (Lock_time), and the number of rows scanned (rows_examined) of the slow query statement.

Question: How to query the number of current slow query statements?

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

Enter the command: show global status like '%slow%';

(Note: For all the above commands, if the parameters are set through the MySQL shell, if MySQL is restarted, all the set parameters will be invalid. If you want to take effect permanently, Configuration parameters need to be written into the my.cnf file).

Additional knowledge point: How to use MySQL's own slow query log analysis tool mysqldumpslow to analyze logs?

perlmysqldumpslow –s c –t 10 slow-query.log

The specific parameter settings are as follows:

-s indicates how to sort, c, t, l, r They are sorted according to the number of records, time, query time, and the number of records returned. ac, at, al, ar represent the corresponding flashbacks;

-t means top, and the following data represents the return How many lines in front;

-g can be followed by regular expression matching, which is not case-sensitive.

The meaning of the above parameters is as follows:

Count:414 The statement appears 414 times;

Time =3.51s (1454) The maximum execution time is 3.51s, and the cumulative total time consumed is 1454s;

Lock=0.0s (0) The maximum time waiting for the lock is 0s, and the cumulative waiting time for the lock is 0s;

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

(Note: the mysqldumpslow script is written in perl language, specifically mysqldumpslow The usage will be discussed later)

Question: In the actual learning process, how do you know that the slow query set is effective?

It’s very simple. We can manually generate a slow query statement. For example, if the value of our slow query log_query_time is set to 1, we can execute the following statement:

selectsleep(1);

This statement is the slow query statement. After that, you can check whether there is this statement in the corresponding log output file or table.

The above is the detailed content of Simple analysis of MySQL general query log and slow query log. For more information, please follow other related articles on the PHP Chinese website!