MySQL enables slow query logs

Source: Internet
Author: User
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:

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.