The difference between normal and slow query logs in MySQL

Intro

<br/>

A previous sharing mentioned that MySQL log types can be divided into Three types, error log (error_log), query log (query_log), and binary log (binary_log). For the introduction and usage of error_log, see error_log in MySQL log. This article mainly introduces the principles and usage of general_log and slow_query_log.

General query log

The general query log (general_log) is mainly used to query the relevant information when each client connects to the database and the SQL statements executed on the database. Configuration method For:

1. Check whether the general query log is enabled and its storage path:

mysql> show variables like "general_log%";
+------------------+------------------------------+| Variable_name    | Value                        |
+------------------+------------------------------+| general_log      | OFF                           |
| general_log_file | /var/lib/mysql/localhost.log |
+------------------+------------------------------+
2 rows in set (0.00 sec)

general_log and general_log_file## in the screen output #Records the current switch status and path of the general query log.

2. Turn on the general query log

    set global general_log = ON

Whether the query is successful:

mysql> show variables like "general_log%";
+------------------+------------------------------+| Variable_name    | Value                        |
+------------------+------------------------------+| general_log      | ON                           |
| general_log_file | /var/lib/mysql/localhost.log |
+------------------+------------------------------+
2 rows in set (0.00 sec)

Success.

Exit and see if it is generated and recorded correctly:

[root@localhost ~]# cat /var/lib/mysql/localhost.log
/usr/sbin/mysqld, Version: 5.7.19 (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
2017-08-14T09:33:11.364650Z     5 Quit  
2017-08-14T09:33:20.671113Z     6 Connect   root@localhost on  using Socket
2017-08-14T09:33:20.671323Z     6 Query select @@version_comment limit 1
2017-08-14T09:33:22.964610Z     6 Query show variables like '%log%'
2017-08-14T09:33:24.574224Z     6 Quit

Success, and we can see that the log not only records SQL commands, but also includes execution time, Id, command type, etc. Related data

Slow query log

The slow query log records statements whose SQL statement time exceeds the preset long_query_time. When the amount of data is large, you can Look at the slow query log to see which statements need to be optimized.

1. Configuration of slow query log

The configuration of slow query log can be found in the database configuration file

my.cnf:

[root@localhost ~]# cat /etc/my.cnf | grep -E 'long|slow|not_using_indexes'long_query_time = 1#log-slow-queries = /var/log/mysql/slow.loglog_queries_not_using_indexes

You can see:

long_query_time = 1 means that if the execution time of the SQL statement exceeds 1s, it will be recorded;
log-slow- queries = /var/log/mysql/slow.log is the setting of the slow query log path;
log_queries_not_using_indexes means statements that do not use indexes, which can reduce the target of recording

The actual configuration is the same as the general query log. Enter the command in the mysql interactive interface to enable the slow query log:

    set slow_query_log = ON

2. Whether the query is successful

[root@localhost ~]# cat /var/lib/mysql/localhost-slow.log
/usr/sbin/mysqld, Version: 5.7.19 (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument

is successful.

But note that slow_query_log may record statements related to user permissions and passwords, so when using slow query log files, please pay attention to the safe storage of the logs.

The above is the detailed content of The difference between normal and slow query logs in MySQL. For more information, please follow other related articles on the PHP Chinese website!