Analysis of MySQL general query log and slow query log

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 the established client connections and executed statements.

2. Slow query log: records all queries that take longer than longquerytime seconds to execute or queries that do not use indexes

1. General query log

When learning general log queries, 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. If the value of general_log is ON, it is enabled; if it is OFF, it is disabled (it is disabled by default).

2) 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) or TABLE (mysql.general_log stored in the database)

Question: How do I enable the MySQL general query log and how do I 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 the general log output to table mode: set global log_output='TABLE';

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

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

(Note: The above command is only effective for the current time. It will become invalid when MySQL is restarted. If you want it to be effective permanently, you need to configure my.cnf)

The configuration of my.cnf file is as follows:

general_log=1 #1 means enabling general log query, 0 means disabling general log query

log_output=FILE,TABLE#Set the output format of general log to file and table

2. Slow query log

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

Generally speaking, slow queries occur in large tables (for example, a table with millions of data) and the fields of the query conditions are not indexed. At this time, the fields that match the query conditions will be scanned in the entire table. If the time spent exceeds long_query_time, it 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 disable the slow query log.

(2) The value of slow_query_log_file is the file in which the slow query log is recorded (note: the default name is hostname.log. Whether the slow query log is written to a specified file requires that the output log format of the slow query be specified as a file. The relevant command is: show variables like '%log_output%'; to view the output format).

(3) long_query_time specifies the slow query threshold. If the execution time of a statement exceeds this threshold, it is considered 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 logged (Note: If you only set log_queries_not_using_indexes to ON and set slow_query_log to OFF, this setting will not 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 usually temporarily enabled during performance tuning.

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

Through the command: show variables like '%log_output%';

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

#Slow query log output to the table (ie mysql.slow_log)
set globallog_output='TABLE';
#Slow query log is only output to text (i.e. the file specified by slow_query_log_file)
setglobal log_output='FILE';
#Slow query log is output to text and table at the same time setglobal log_output='FILE,TABLE';

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

The slow query log is recorded in the myql.slow_log table in the following format:

Slow query logs are recorded in the hostname.log file in the following format:

As you can see, both the table and the file record the following information: which statement caused the slow query (sql_text), the query time of the slow query statement (query_time), the table lock time (Lock_time), and the number of scanned rows (rows_examined).

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

In MySQL, there is a variable that specifically 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 become invalid. If you want to make them effective permanently, you need to write the configuration parameters into the my.cnf file).

Additional knowledge point: How to use MySQL’s built-in slow query log analysis tool mysqldumpslow to analyze logs?

perlmysqldumpslow –sc –t 10 slow-query.log

The specific parameter settings are as follows:

-s indicates the sorting method. c, t, l, and r are sorted by the number of records, time, query time, and number of records returned, respectively. ac, at, al, and ar indicate the corresponding reverse sorting.

-t means top, and the data following it indicates how many records are returned.

-g can be followed by a regular expression match, which is case insensitive.

The meanings of the above parameters are as follows:

Count:414 The statement appeared 414 times;

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

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

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

(Note: The mysqldumpslow script is written in Perl language. The specific usage of mysqldumpslow will be discussed later.)

Question: In the actual learning process, how do you know that the slow query you 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. Afterwards, you can check whether there is such a statement in the corresponding log output file or table.

Summarize

The above is what I introduced to you about MySQL general query log and slow query log analysis. I hope it will be helpful to you. If you have any questions, please leave me a message and I will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!