MySQL slow query log configuration and use tutorial, mysql Log use tutorial

MySQL slow query log configuration and use tutorial, mysql Log use tutorial

Preface

MySQL slow query log is a feature we often encounter in our daily work. MySQL slow query log provides query information that exceeds the specified time threshold and provides a major reference for performance optimization, is a very practical function, MySQL slow query log is very simple to enable and configure, you can specify the recorded file (or table ), when the time threshold is exceeded, the slow SQL statement can be recorded. To be honest, compared with the trace or extended events of sqlserver (although the roles of the two are not just the same ), the configuration of MySQL is always refreshing.

I. Opening slow query logs

Under normal circumstances, you only need to addslow_query_log = 1Configuration: opens the slow query log. If slow_query_log_file is not specified, a file with the host name + 'low'. log is automatically generated.

　　

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

　　

By defaultslow_query_log = 1When MySQL is started, slow query is enabled. A default file named host name + + 'low'. log is automatically generated to record slow queries that exceed 10 s.

You can also explicitly specify the name of the slow query log file (automatically created if no log file exists) and the time threshold for recording slow query (not the default 10 s ).

　　

Note:When long_query_time is specified in the configuration file, there is no time unit and only one value is required. For example, 1 represents 1 s. If the time unit is included, the service cannot be started.

　　

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

　　

3. Record slow query logs to tables

Configuration: You need to add a log_output configuration to record the slow query to the table.

There is a default slow_log table under the mysql database.slow_query_log_file = slow_logTo record slow query logs in the table.

　　

The recorded slow SQL statement 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.

　　

Differences between log files recorded in slow queries and tables:

1. When a slow query record is recorded in a log file or table, the record itself is not much different. If it is recorded in the table, the execution time of the slow query cannot be accurate to the subtle,

2. If the slow query information is recorded in the table, it is easy to query. However, because it is structured data, it may be recorded in the slow query log file (flat text file) it takes a little longer (I guess). If it is recorded in a file, it needs to be parsed by mysqldumpslow.

3. Slow queries do not record query failures. For example, long_query_time is set to 10 (10 seconds). A Query lasts for more than 10 seconds, but fails to be executed for other reasons, mySQL slow query cannot record this query information.

Summary

The above is all the content of this article. I hope the content of this article will help you in your study or work. If you have any questions, please leave a message, thank you for your support.