Enable slow query in mysql in linux _ MySQL-mysql tutorial

In linux, mysql enables slow queries. mysql statements that affect the query speed are slow, it may be that the write is not reasonable or the multi-table joint query in big data, etc. Therefore, we need to find these statements, analyze the causes, and optimize them.

1. Method 1: enable slow query with commands

1) view the default slow query time:

mysql> show variables like "%long%";+--------------------+-----------+| Variable_name      | Value     |+--------------------+-----------+| long_query_time    | 10.000000 || max_long_data_size | 1048576   |+--------------------+-----------+2 rows in set (0.24 sec)

2) modify the slow query time. it is recommended to set it to within 5 seconds. do not set it to too large. Otherwise, the slow query function will be lost.

mysql> set global long_query_time=2;Query OK, 0 rows affected (0.25 sec)

Here, the slow query time is set to 2 seconds.

3) Check whether slow query is enabled

mysql> show variables like "%slow%";+---------------------+-----------------------------------------+| Variable_name       | Value                                   |+---------------------+-----------------------------------------+| log_slow_queries    | OFF                                     || slow_launch_time    | 2                                       || slow_query_log      | OFF                                     || slow_query_log_file | /usr/local/mysql/var/li411-195-slow.log |+---------------------+-----------------------------------------+4 rows in set (0.23 sec)

We can see that the slow query is not enabled.

4) enable slow query

mysql> set slow_query_log='ON';ERROR 1229 (HY000): Variable 'slow_query_log' is a GLOBAL variable and should be set with SET GLOBAL

An error is reported. Note that the global keyword must be added during configuration.

mysql> set global slow_query_log='ON';Query OK, 0 rows affected (1.11 sec)

Check whether it is enabled again:

mysql> show variables like "%slow%";+---------------------+-----------------------------------------+| Variable_name       | Value                                   |+---------------------+-----------------------------------------+| log_slow_queries    | ON                                      || slow_launch_time    | 2                                       || slow_query_log      | ON                                      || slow_query_log_file | /usr/local/mysql/var/li411-195-slow.log |+---------------------+-----------------------------------------+4 rows in set (4.04 sec)

We can see that the mysql slow query has taken effect. 2. Method 2: modify the mysql configuration file my. cnf and add it to the [mysqld] section:

long_query_time = 2log-slow-queries = /usr/local/mysql/var/localhost-slow.log

Restart the mysql service.

Note: In the lnmp environment, use/etc/init. d/mysql reload command to reload mysql service does not successfully enable slow query, use/etc/init. d/mysql restart command successfully enabled slow query.