MySQL slow query log configuration and detailed usage tutorial

The slow query log is used to record some query statements that are too slow, which can help administrators analyze the problem. The following article mainly introduces the configuration and usage tutorial of MySQL slow query log. It is introduced through sample code. Very detailed, friends in need can refer to it.

Preface

MySQL slow query log is a function that we often encounter in our daily work. MySQL slow query log provides more than The query information of the specified time threshold provides the main reference for performance optimization. It is a very practical function. It is very simple to open and configure the MySQL slow query log. You can specify the recorded file (or table), the exceeded time threshold, etc. You can record slow sql. To be honest, compared with sqlserver's trace or extended events (although these two are not the only ones), MySQL's configuration always gives people a very refreshing feeling.

1. Opening the slow query log

Under normal circumstances, you only need to add slow_query_log = 1## in the configuration file #Configuration, that is, turning on the slow query log. If slow_query_log_file is not specified, a file with the host name + 'slow'.log will be automatically generated.

　

2. By default, the time threshold for recording slow queries is 10s

　

By default, when you specify

slow_query_log = 1 and start MySQL, you can open the slow query and automatically generate a default file with the host name ++'slow'.log for recording. Exceeds slow queries that execute for more than 10 seconds.

You can also explicitly specify the slow query log file name (it will be automatically created if it does not exist) and the time threshold for recording slow queries (non-default 10s).

　

Note: When specifying long_query_time in the configuration file, no time unit is required, only a value is required, such as 1 It means 1s. If the time unit is specified, the service will not be started.

　

The following is an example of slow sql recorded in the log file

　

三, Record slow query logs to the table

Configuration: You need to add a log_output configuration to record slow queries into the table

There is a default slow_log table under the mysql library. You can directly set

slow_query_log_file = slow_log to record slow query logs into the table.

　

The recorded slow sql is as follows. It can be found that sql_text is a binary information, not the original sql text

　

You can convert it through the CONVERT function.

　

About the difference between slow query recording in log files and tables:

　1. Slow query Recorded to log files and tables, the record itself is not much different. If it is recorded in the table, the execution time information of the slow query cannot be accurate to the subtle.

2. If the slow query information is recorded in the table, It is convenient for querying, but because it is structured data, it may be a little slower than recording in a slow query log file (flat text file) (personal guess). If it is recorded to a file, the mysqldumpslow tool needs to be parsed.

　3. Slow queries do not record queries that fail to execute. For example, long_query_time is set to 10 (10 seconds). If a query exceeds 10 seconds, but fails to execute due to other reasons, MySQL's slow queries will not be recorded. This query information.

Summarize

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