Shulou(Shulou.com)05/31 Report--

Editor to share with you the Mysql general query log and slow query log example analysis, I believe that most people do not understand, so share this article for your reference, I hope you will learn a lot after reading this article, let's go to know it!

The logs in MySQL include error log, binary log, general query log, slow query log, and so on. This paper mainly introduces two commonly used functions: general query log and slow query log.

1. General query log: record the 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

When learning generic log queries, you need to know the commands commonly used in 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, and if it is OFF, it is off (off by default).

2) show variables like'% log_output%'

View the format of the current slow query log output, either FILE (hostname.log stored in the data file of the database) or TABLE (mysql.general_log stored in the database)

Question: how to open the MySQL generic query log and how to set the generic log output format to be output?

Enable general log query: set global general_log=on

Turn off generic log query: set global general_log=off

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

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

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

(note: the above command only takes effect for the current time. When the MySQL restart fails, you need to configure my.cnf if you want to take effect permanently.)

The my.cnf file is configured as follows:

General_log=1 # 1 means to enable general log query, and a value of 0 means to turn off general log query

Log_output=FILE,TABLE# sets the output format of the general log to files and tables

Second, slow query log

MySQL's slow log is a kind of log record provided by MySQL, which is used to record statements whose response time in MySQL exceeds the threshold. Specifically, SQL whose running time exceeds the long_query_ time value will be recorded in the slow log (logs can be written to files or database tables. If high performance requirements are required, it is recommended to write files). By default, slow log is not enabled in MySQL database, and the default value of long_query_time is 10 (that is, 10 seconds, usually set to 1 second), that is, statements running for more than 10 seconds are slow query statements.

Generally speaking, a slow query occurs in a large table (for example, there are millions of data in a table), and the fields of the query conditions are not indexed. In this case, the fields to match the query conditions are scanned throughout the table, and it takes time to check long_query_time, then it is a slow query statement.

Question: how to check whether the current slow log is open?

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 slow log, and OFF is to disable slow log.

(2) the value of slow_query_log_file is the recorded slow log to the file (Note: the default name is hostname.log. If the slow log is written in the specified file, you need to specify that the output log format of slow query is file, and the related command is: show variables like'% log_output%'; to check the output format).

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

(4) if the value of log_queries_not_using_indexes is set to ON, all queries that do not take advantage of the index will be recorded (Note: if only log_queries_not_using_indexes is set to ON and slow_query_log is set to OFF, this setting will not take effect, that is, the setting takes effect if the value of slow_query_log is set to ON). Generally, it will be temporarily enabled during performance tuning.

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

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

You can see the format of the output by the value of log_output, and the value above is TABLE. Of course, we can also format the output as text, or record both the text and the database table with the following commands:

# slow log output to table (i.e. mysql.slow_log) set globallog_output='TABLE';# slow log output only to text (i.e. files specified by slow_query_log_file) setglobal log_output='FILE';# slow log output to both text and table setglobal log_output='FILE,TABLE'

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

In the myql.slow_ log table of slow query, the format is as follows:

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

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

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

There is a variable in MySQL 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 shell of MySQL, if you restart MySQL, all the set parameters will become invalid. If you want to take effect permanently, you need to write the configuration parameters into the my.cnf file.

Supplementary knowledge: 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 parameters are set as follows:

-s indicates how it is sorted. C, t, l, r are sorted by the number of records, time, query time, and the number of records returned. Ac, at, al, and ar indicate the corresponding flashbacks.

-t means top, followed by data indicating how many previous entries are returned.

Regular expression matching can be written after-g, which is case-insensitive.

The parameters in the above are as follows:

The Count:414 statement appears 414 times

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

The longest waiting time of Lock=0.0s (0) for lock is 0s, and the cumulative waiting time for lock is 0s.

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

(note: the mysqldumpslow script is written in Perl, the specific usage of mysqldumpslow will be discussed later.)

Question: in fact, in the learning process, how to know that the set slow query is effective?

Quite simply, 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 a slow query statement, and then you can check whether it exists in the corresponding log output file or table.

The above is all the contents of the article "sample Analysis of Mysql General query Log and slow query Log". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.