MySQL enables slow query logs

Slow query: the query time of an SQL statement exceeds the predefined query time. These statements must be recorded for statement optimization. The following describes how to record slow queries.

Slow query: the query time of an SQL statement exceeds the predefined query time. These statements must be recorded for statement optimization. The following describes how to record slow queries.

The so-called slow query means that the query time of an SQL statement exceeds the predefined query time. These statements must be recorded for statement optimization, the following describes how to record slow query statements:

1. Check whether the slow query log function is Enabled:

Mysql> show global variables like 'slow _ query_log ';

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

| Variable_name | Value |

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

| Slow_query_log | OFF |

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

1 row in set (0.00 sec)

If the SQL command is not enabled, you can open it directly on 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; # enable the slow query log record command

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

| Variable_name | Value |

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

| Slow_query_log | ON | # The value of slow_query_log is on, indicating that the function has been enabled.

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

1 row in set (0.00 sec)

2. Define the time-out period for SQL query:

View the default number of seconds:

Mysql> show global variables like 'long _ query_time ';

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

| Variable_name | Value |

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

| Long_query_time | 10.000000 | # The default value is 10 seconds. The time is too long. 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 | # Set the timeout value to 2 seconds.

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

1 row in set (0.00 sec)

3. view the log storage path for slow query:

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 results:

Run the following command to sleep for 4 seconds:

Mysql> select sleep (4 );

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

| Sleep (4) |

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

| 0 |

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

1 row in set (4.00 sec)

Then, check whether the slow query log is recorded in the slow query log:

[Root @ node9 ~] # Cat/mydata/data/node9-slow.log

# Time: 150408 9:22:41

# User @ Host: 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); # It has been recorded in the log. The generated environment can record slow query statements, and optimize SQL query statements as appropriate, for example, using the where statement or like statement for exact query.

This article permanently updates the link address: