MySQL Slow query log: A log-logging feature provided by MySQL

What is the slow query log for MySQL? Definition: MySQL provides a logging function to record the MySQL corresponding time over the long_query_time value of the SQL statement, long_query_time default value of 10 seconds, the corresponding time of more than 10 seconds of SQL will be recorded in the log.

1, check the status of slow log function, slow log function is turned off by default

Show variables like '%slow_query_log% ';

Off indicates that slow logging is turned off, slow logging is stored in the Desktop-2331b4v-slow.log file, and desktop is my computer name.

2. Turn on slow log function

Set global slow_query_log=1;

Then query the slow log status
The status is on to indicate that the slow log function is turned on.

3. Check the default Long_query_time time
The Long_query_time is 10 seconds, indicating that the sq statement exceeds 10 seconds before it is recorded in the slow log.

4, set long_query_time time, the default of 10 seconds is too long

Set global long_query_time=3;

Re-open a session, if it is a window system, then re-punch a cmd (note If you are still in the set Long_query_time session query Long_query_time length, or the default of 10 seconds, so to restart a session), Query Long_query_time time again

5. Simulate time-consuming SQL
The SQL statement represents a query for 5 seconds, exceeding the set long_query_time time. Then open

Desktop-2331b4v-slow.log log, the contents are as follows: MySQL, version:5.5.60 (MySQL Community Server (GPL)). Started With:tcp port:3306, Named pipe:mysqltime                 Id Command    argument# time:180729 12:52:29# User@host:root[roo T] @ localhost [127.0.0.1]# query_time:5.005746  lock_time:0.000000 rows_sent:1  rows_examined:0set timestamp= 1532839949;select sleep (5);

It is known that the time-out SQL has been written into the slow log.

Slow log comparison, you can also query the number of bars in the slow log

6. Configuration

The above example is configured with a command, and if the database restarts, the configuration is invalidated. If the configuration is still valid after rebooting, you can add the configured command under My.ini [mysqld]:

Slow_query_log=1slow_query_log_file=d:/mysql/my.loglong_query_time=3long_output=file

Related articles:

MySQL Slow query log analysis

Using Mysqldumpslow and Mysqlsla to analyze the MySQL slow query log

Related videos:

MySQL Data management backup recovery Case resolution video tutorial