Enable slow query in mysql in linux

The statements that affect the query speed in mysql are very slow. These slow statements may be written improperly or the Combined Query of multiple tables in big data, 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.