mysql slow query log: a logging function provided by mysql

What is the slow query log of mysql? Definition: A logging function provided by mysql. It is used to record SQL statements whose corresponding time exceeds the long_query_time value. The default value of long_query_time is 10 seconds. SQL whose corresponding time exceeds 10 seconds will be recorded in the log.

1. Check the status of the slow log function. The slow log function is turned off by default.

 show variables like '%slow_query_log%';

OFF indicates that the slow log function is turned off. The slow log records are stored in DESKTOP In the -2331B4V-slow.log file, DESKTOP is my computer name.

2. Turn on the slow log function

set global slow_query_log=1;

Then check the slow log status
The status is ON, which means the slow log function is turned on.

3. Check the default long_query_time time
long_query_time is 10 seconds, which means that SQ statements that exceed 10 seconds will be recorded in the slow log.

4. Set long_query_time, the default 10 seconds is too long

set global long_query_time=3;

Reopen a session, if it is a window system, just punch in a cmd again (note that if you are still setting long_query_time If you query the long_query_time duration in a session, it will still be 10 seconds by default, so you need to restart a session) and query the long_query_time duration again

5. Simulation time-consuming sql
The The sql statement indicates that the query took 5 seconds, which exceeds the set long_query_time. Then open

DESKTOP-2331B4V-slow.log日志，内容如下：
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[root] @ localhost [127.0.0.1]
# Query_time: 5.005746  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0SET timestamp=1532839949;select sleep(5);

and you will see that the timeout SQL has been written into the slow log.

When there are many slow logs, you can also query the number of slow logs

6. Configuration

The above example is configured with the command , if the configuration becomes invalid after the database is restarted. If you want the configuration to remain valid after restarting, you can add the configuration command to [mysqld] under my.ini:

slow_query_log=1slow_query_log_file=d:/mysql/my.loglong_query_time=3long_output=FILE

Related articles:

MySQL Slow Query Log analysis

Use mysqldumpslow and mysqlsla to analyze mysql slow query log

Related videos:

MySQL data management backup and recovery Case analysis video tutorial

The above is the detailed content of mysql slow query log: a logging function provided by mysql. For more information, please follow other related articles on the PHP Chinese website!