In-depth understanding of MySQL slow query log

What is the slow query log?

The MySQL slow query log is a log record provided by MySQL. It is used to record statements in MySQL whose response time exceeds the threshold. Specifically, SQL statements whose running time exceeds the long_query_time value will be recorded in the slow query log.

Specifically, SQL statements that take longer than the long_query_time value will be recorded in the slow query log. The default value of long_query_time is 10, which means that the statement will run for more than 10 seconds.

It is used to check which SQL statements exceed our maximum tolerance time. For example, if a SQL statement takes more than 5 seconds to execute, we will consider it a slow SQL statement. We hope to collect SQL statements that take more than 5 seconds and conduct a comprehensive analysis based on the previous explain.

By default, the MySQL database does not have the slow query log enabled, so we need to set this parameter manually. Of course, if it is not necessary for tuning, it is generally not recommended to enable this parameter, because enabling slow query logs will more or less have a certain performance impact. Slow query log supports writing log records to files!

How to enable slow query

Check the open status

SHOW VARIABLES LIKE '%slow_query_log%'

Enable slow query

set global slow_query_log = 1

Use set global_slow_query_log = 1 to enable the slow query log, which only applies to the current database and will become invalid if MYSQL is restarted.

If you want to make it permanent, you must modify the configuration file my.cnf (the same applies to other system variables). Modify the my.cnf file and add or modify parameters under [mysqld].

Then restart the MySQL server. The following two lines will be configured in the my.cnf file

slow_query_log = 1

slow_query_log_file=/var/lib/mysql/tim-slow.log

Regarding the slow query parameter slow_query_log_file, it specifies the storage path of the slow query log file. The system will give a default file host_name-slow.log by default (if the parameter slow_query_log_file is not specified)

So after the slow query log is enabled, what kind of SQL will be recorded in the slow query?

Use show variables like 'long_query_time%' to view the default time length in seconds:

Similarly, you can use commands to modify it or configure it in my.cnf. If the running time is exactly equal to long_query_time, it will not be recorded. That is to say, in the MySQL source code, it is judged to be greater than long_query_time, not greater than or equal to!

To set the logging threshold:

set global long_query_time=3;

Similarly, you can use commands to modify it or configure it in my.cnf. If the running time is exactly equal to long_query_time, it will not be recorded. That is to say, in the MySQL source code, it is judged to be greater than long_query_time, not greater than or equal to!

To set the logging threshold:

set global long_query_time=3;

Set it but no changes occurred? Why? At this time, you need to restart a session:

Next, we will perform a slower query, as shown below, but remember to configure it in the configuration file as follows:

Then go to the log file to check which SQL statements exceed the threshold:

Query how many slow query records there are in the current system:

Record my configuration file

slow_query_log=1;
slow_query_log_file=/var/lib/mysql/tim-slow.log;
long_query_time=3;
log_output=FILE

Log analysis tool mysqldumpshow

In a production environment, if you want to manually analyze logs, find and analyze SQL, it is obviously a manual job. MYSQL provides a log analysis tool mysqldumpshow

• s: indicates the sorting method
• c: Number of visits
• l: Lock time
• r: Return records
• t: query time
• al: Average lock time
• ar: average number of returned records
• at: average query time
• t: how many records are returned
• g: followed by a regular matching pattern, case-insensitive
  

Here is an example of usage:

Get the 10 SQL statements that return the most records

mysqldumpslow -sr -t 10 /var/lib/mysql/tim-slowlog

Get the top 10 SQL queries

mysqldumpslow -s ct 10 /var/lib/mysql/tim-slow log

Get the first 10 items sorted by time, which contain left join query statements

mysqldumpslow -st -t 10 -g "left join" /var/lib/mysql/tim-slowlog

It is also recommended to use these commands in combination with more, otherwise the screen may explode.

mysqldumpslow -sr -t 10 /var/lib/mysq/tim-slow.log | more

The above is the detailed content of the in-depth understanding of MySQL slow query log. For more information about MySQL slow query log, please pay attention to other related articles on 123WORDPRESS.COM!