> For the complete documentation index, see [llms.txt](https://knowledge.tracelog.in/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://knowledge.tracelog.in/database/mysql/mysql-enable-slow-query-log.md).

# MySQL - enable slow query log

### What is slow query log?

* Slow queries can affect database and server performance. 
* The slow query log consists of SQL statements that took more than `long_query_time` seconds to execute. 
* This greatly simplifies the task of finding inefficient or time-consuming queries.
* **By default, the slow query log is disabled**

### How to enable?

1. Login to MySQL, database `abc_prod`

   ```sql
    shell> mysql -u root -p abc_prod
   ```
2. Enable slow query log

   ```sql
    mysql> SET GLOBAL slow_query_log = 'ON';
   ```
3. Check path to log file

   ```sql
    mysql> SHOW VARIABLES LIKE 'slow_query_log_file';
    +---------------------+-----------------------------+
    | Variable_name       | Value                       |
    +---------------------+-----------------------------+
    | slow_query_log_file | /var/lib/mysql/db3-slow.log |
    +---------------------+-----------------------------+
    1 row in set (0.00 sec)
   ```
4. Change long query time to 5 seconds - **default is 10 seconds**

   ```sql
    mysql> SET GLOBAL long_query_time = 5;
   ```
5. Logout MySQL session then login again
6. Take a small test to ensure slow query log is enable

   ```sql
    mysql> SELECT SLEEP(10);
   ```
7. Check sleep on slow query log

   ```
    root@db3:/home/ubuntu:~$ cat /var/lib/mysql/db3-slow.log
   ```

   ```sql
    /usr/sbin/mysqld, Version: 5.6.35-log (MySQL Community Server (GPL)). started with:
    Tcp port: 3306  Unix socket: /var/run/mysqld/mysqld.sock
    Time                 Id Command    Argument
    # Time: 170321  7:15:52
    # User@Host: root[root] @ localhost []  Id:  1160
    # Query_time: 7.000249  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
    SET timestamp=1490080552;
    SELECT SLEEP(7);
    # Time: 170321  7:17:31
    # User@Host: root[root] @ localhost []  Id:  1161
    # Query_time: 10.000215  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
    use abc_prod;
    SET timestamp=1490080651;
    SELECT SLEEP(10);
   ```

**Most important:**

Using `mysqldumpslow` tool for summarize slow query log files

```
root@db3:/home/ubuntu:~$ mysqldumpslow /var/lib/mysql/db3-slow.log
```

```sql
Reading mysql slow query log from /var/lib/mysql/db3-slow.log
Count: 2  Time=8.50s (17s)  Lock=0.00s (0s)  Rows=1.0 (2), root[root]@localhost
  SELECT SLEEP(N)
```

### How to turn it off?

```sql
mysql> SET GLOBAL slow_query_log = 'OFF';
```


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://knowledge.tracelog.in/database/mysql/mysql-enable-slow-query-log.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.