MySQL Method of Opening Slow Query Log

1.1 Introduction

Open the slow query log, which can make MySQL record the statements that query more than the specified time. By locating the bottleneck of analysis performance, we can better optimize the performance of the database system.

1.2 Log in to the database to view

[root@localhost lib]# mysql  In fact, in fact, the uroot

Because there is no setting password, those who have a password will receive the password in mysql uroot p

1.2. 1 Enter MySql to query whether slow query is opened

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:

slow_query_log Slow query on status OFF not on ON on slow_query_log_file Slow query log storage location (this directory needs the writable permission of the running account of MySQL, and is generally set as the data storage directory of MySQL)

1.2. 2 View 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 How many seconds did the query exceed before recording the default 10 seconds changed to 1 second

1.3 Modification Method 1: (Not Recommended)

Method 1: Advantages: Temporary start slow query, no need to restart the database Disadvantages: MySql restart slow query failure

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

By default, the value of slow_query_log is OFF, which means that slow query logging is disabled. It can be turned on by setting the value of slow_query_log, as follows:: Whether to turn on slow query logging, 1 means turn on and 0 means turn off.

1.3. 1 Check whether to open slow query

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)

Modification of input statement (invalid after reboot, it is recommended that the modification take effect permanently in/etc/my. cnf)

mysql> set global slow_query_log=1;

Query OK, 0 rows affected (0.11 sec)

1.3. 2 View 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 path of my. cnf, so you can use find to find it

Remarks: My MySQL is compiled in a path of/etc/my. cnf (1 is always 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 It's all here.) 

1.4. 1.1 Amendment

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

Add below [mysqld]

slow_query_log =1

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

long_query_time = 1

Parameter description:

slow_query_log Slow Query Open State 1 is on slow_query_log_file Slow Query Log Location long_query_time How many seconds did the query exceed before recording the default 10 seconds changed to 1 second

Restart MySQL after modification

1.5 View, 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 View slow query log

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)

0

1.5. 1.3 See how many slow queries there are through the MySQL command

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)

1

1.6 Log Analysis Tool mysqldumpslow

In the 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 mysqldumpslow