MySQL slow query log configuration and usage tutorial

Preface

MySQL slow query log is a function that we often encounter in our daily work. MySQL slow query log provides query information that exceeds the specified time threshold, which provides the main reference for performance optimization. It is a very practical function. The opening and configuration of MySQL slow query log is very simple. You can specify the recorded file (or table), and the time threshold exceeded can be recorded to the slow SQL. To be honest, compared with sqlserver trace or extended events (although the functions of these two are not just that), MySQL 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 configuration in the configuration file to open 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 slow_query_log = 1 is specified, slow query is enabled when MySQL is started, and a default file named host name++'slow'.log is automatically generated to record slow queries that take more than 10 seconds to execute.

You can also explicitly specify the name of the slow query log file (it will be created automatically 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, such as 1 for 1s. If a time unit is included, the service will not start.

The following is an example of a slow SQL statement recorded in a log file:

3. Record slow query logs to a table

Configuration: You need to add a log_output configuration to record slow queries in 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 the slow query log in 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 use the CONVERT function to convert it.

The difference between slow queries recorded in log files and in tables:

1. Slow queries are recorded in log files and tables. The records themselves are not much different. If they are recorded in tables, the execution time information of slow queries cannot be accurate to microseconds.

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

3. Slow query does not record failed queries. For example, if long_query_time is set to 10 (10 seconds), and a query takes more than 10 seconds but fails for other reasons, MySQL slow query will not be able to record this query information.

Summarize

The above is the full content of this article. I hope that the content of this article can bring some help to your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support of 123WORDPRESS.COM.