MySQL open slow query under Linux

The most affected speed in MySQL is those with very slow queries. These slow statements may not be reasonable enough to write, or a joint query of multiple tables under Big data, and so on. So we need to find these statements, analyze the reasons, and optimize them.

1, Method 1: Open the slow query with the command

1), check 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), change the slow query time, the proposed set to 5 seconds or less, do not set too big, or lose the role of slow query.

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

This sets the slow query time to 2 seconds.

3), check if slow query is turned on

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 turned on.

4), turn on slow query

Mysql> set slow_query_log= ' on '; ERROR 1229 (HY000): Variable ' Slow_query_log ' is a GLOBAL Variable and should BES set with set GLOBAL

An error has been made. It is important to note that you need to add global keyword when setting up.

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

Check again to see if it is turned on:

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 is already in effect.

2, Method 2: Change the MySQL configuration file my.cnf in the [Mysqld] segment to add:

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

Restarting the MySQL service is possible.

Note here: Your own in the LNMP environment through the/etc/init.d/mysql Reload command overloaded MySQL service did not successfully open slow query, using the/etc/init.d/mysql Restart command to successfully open a slow query.

MySQL open slow query under Linux