MySQL turn on slow query log

The so-called slow query is the query time of a SQL statement more than the predefined well-defined query time, these statements are to be recorded for the use of statement optimization, the following is how to record the slow query statement method:

1, first check whether to turn on the slow query log function:

Mysql> show global variables like ' slow_query_log ';

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

| variable_name | Value |

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

| Slow_query_log | OFF |

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

1 row in Set (0.00 sec)

If it is not enabled, it can be opened directly in the SQL command interface:

mysql> set global slow_query_log=1;

Query OK, 0 rows Affected (0.00 sec)

Mysql> show global variables like ' slow_query_log '; #开启慢查询日志记录的命令

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

| variable_name | Value |

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

| Slow_query_log |  On | # The value of Slow_query_log is on to indicate that it is turned on

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

1 row in Set (0.00 sec)

2. Define time-out time for SQL queries:

See how many seconds the default is:

Mysql> show global variables like ' long_query_time ';

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

| variable_name | Value |

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

| Long_query_time |  10.000000 | #默认为10秒, the time is too long, I set it to 2 seconds

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

1 row in Set (0.00 sec)

mysql> Set Global long_query_time = 2;

Query OK, 0 rows Affected (0.00 sec)

Mysql> show global variables like ' long_query_time ';

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

| variable_name | Value |

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

| Long_query_time | 2.000000 | #将超时时间设置为2秒

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

1 row in Set (0.00 sec)

3. View the save path of the slow query log:

Mysql> show global variables like ' slow_query_log_file ';

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

| variable_name | Value |

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

| Slow_query_log_file | /mydata/data/node9-slow.log |

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

1 row in Set (0.01 sec)

4. Test Result:

Simulate executing a command first sleep for 4 seconds:

Mysql> Select Sleep (4);

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

| Sleep (4) |

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

| 0 |

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

1 row in Set (4.00 sec)

Then check to see if this slow query log will be recorded in the slow query log:

[Email protected] ~]# Cat/mydata/data/node9-slow.log

# time:150408 9:22:41

# [email protected]: root[root] @ localhost [] id:1

# query_time:4.001110 lock_time:0.000000 rows_sent:1 rows_examined:0

SET timestamp=1428456161;

Select Sleep (4); #已经被记录到日志当中, the build environment can record slow query statements, and make proper SQL query statement optimizations, such as using the WHERE statement or like statement to do the exact query.

This article is from the "Linux" blog, so be sure to keep this source http://zhangshijie.blog.51cto.com/806066/1693521

MySQL turn on slow query log