Detailed explanation of how to enable slow query log in MySQL

This article mainly introduces the method of opening slow query log in MySQL. The editor thinks it is quite good. Now I will share it with you and give it as a reference. Let’s follow the editor to take a look, I hope it can help everyone.

1.1 Introduction

Turning on the slow query log allows MySQL to record queries that exceed the specified time. By locating and analyzing performance bottlenecks, the database can be better optimized. system performance.

1.2 Log in to the database to view

[root@localhost lib]# mysql –uroot

Because there is no password set, if you have a password, enter the password in mysql –uroot –p

1.2.1 Enter MySql to check whether slow query is enabled

##

mysql> show variables like 'slow_query%';

+---------------------+--------------------------------------------+

| Variable_name    | Value                   |

+---------------------+--------------------------------------------+

| slow_query_log   | OFF                    |

| slow_query_log_file | /application/mysql/data/localhost-slow.log |

+---------------------+--------------------------------------------+

2 rows in set (0.00 sec)

Parameter description:

1. slow_query_log Slow query opening status OFF Not turned on ON To turn on

2. slow_query_log_file The location where the slow query log is stored (this directory requires the writable permissions of the MySQL running account, generally set to MySQL Data storage directory)

1.2.2 Check the slow query timeout

mysql> show variables like 'long%';

+-----------------+-----------+

| Variable_name  | Value   |

+-----------------+-----------+

| long_query_time | 10.000000 |

+-----------------+-----------+

1 row in set (0.00 sec)

long_query_time The query exceeds How many seconds to record? Default 10 seconds changed to 1 second

1.3 Modification method 1: (not recommended)

Method 1: Advantages: Enable slow query temporarily, no restart required Database Disadvantages: MySql slow query fails after restarting

Recommendation: According to business needs, it is recommended to use the second type, and the first one can be used temporarily

By default, the value of slow_query_log is OFF, indicating slow query The log is disabled and can be enabled by setting the value of slow_query_log, as shown below: Whether to enable the slow query log, 1 means on, 0 means off.

1.3.1 Check whether slow query is enabled

mysql> show variables like '%slow_query_log%';

+---------------------+--------------------------------------------+

| Variable_name    | Value                   |

+---------------------+--------------------------------------------+

| slow_query_log   | OFF                    |

| slow_query_log_file | /application/mysql/data/localhost-slow.log |

+---------------------+--------------------------------------------+

2 rows in set (0.01 sec)

Input statement modification (it will become invalid after restarting, it is recommended to modify it in /etc/my Changes in .cnf will take effect permanently)

mysql> set global slow_query_log=1;

Query OK, 0 rows affected (0.11 sec)

1.3.2 Check again

mysql> show variables like '%slow_query_log%';

+---------------------+--------------------------------------------+

| Variable_name    | Value                   |

+---------------------+--------------------------------------------+

| slow_query_log   | ON                     |

| slow_query_log_file | /application/mysql/data/localhost-slow.log |

+---------------------+--------------------------------------------+

2 rows in set (0.00 sec)

1.4 Modification method 2: (recommended)

Modify MySql slow query, many people don’t know the my.cnf path, you can use find to find it

Remarks: My MySQL is The compiled path is /etc/my.cnf (usually here)

[root@localhost log]# find / -type f -name "my.cnf"

/application/mysql-5.5.51/mysql-test/suite/rpl/my.cnf

/application/mysql-5.5.51/mysql-test/suite/federated/my.cnf

/application/mysql-5.5.51/mysql-5.5.51-linux2.6-x86_64/mysql-test/suite/rpl/my.cnf

/application/mysql-5.5.51/mysql-5.5.51-linux2.6-x86_64/mysql-test/suite/federated/my.cnf

/etc/my.cnf  ###（一般都是这里）

1.4.1.1 Modification

[root@localhost log]# vim /etc/my.cnf

Find [mysqld] and add

slow_query_log =1

slow_query_log_file=/application/mysql/data/localhost-slow.log

long_query_time = 1

Parameter description:

1. slow_query_log slow query open status 1 To enable

2. slow_query_log_file The location where the slow query log is stored

3. long_query_time How many seconds does the query take to record? The default 10 seconds is changed to 1 second

Restart MySQL after modification

1.5 View and test

1.5.1.1 Insert a test slow query

mysql> select sleep(2);

+----------+

| sleep(2) |

+----------+

|    0 |

+----------+

1 row in set (2.00 sec)

1.5.1.2 Check the slow query log

[root@localhost data]# cat /application/mysql/data/localhost-slow.log

/application/mysql/bin/mysqld, Version: 5.5.51-log (MySQL Community Server (GPL)). started with:

Tcp port: 3306 Unix socket: /tmp/mysql.sock

Time         Id Command  Argument

/application/mysql/bin/mysqld, Version: 5.5.51-log (MySQL Community Server (GPL)). started with:

Tcp port: 3306 Unix socket: /tmp/mysql.sock

Time         Id Command  Argument

/application/mysql/bin/mysqld, Version: 5.5.51-log (MySQL Community Server (GPL)). started with:

Tcp port: 3306 Unix socket: /tmp/mysql.sock

Time         Id Command  Argument

# Time: 170605 6:37:00

# User@Host: root[root] @ localhost []

# Query_time: 2.000835 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0

SET timestamp=1496615820;

select sleep(2);

1.5. 1.3 Check how many slow queries there are through MySQL commands

mysql> show global status like '%Slow_queries%';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| Slow_queries | 1   |

+---------------+-------+

1 row in set (0.00 sec)

1.6 Log analysis tool mysqldumpslow

In the production environment, If you want to manually analyze logs, search and analyze SQL, it is obviously a laborious task. MySQL provides the log analysis tool mysqldumpslow

Related recommendations:

MySQL slow query log related Configuration and use

The difference between normal and slow query logs in MySQL

Introduction to MySQL slow query operation for Mysql optimization

The above is the detailed content of Detailed explanation of how to enable slow query log in MySQL. For more information, please follow other related articles on the PHP Chinese website!