Knowledge base
Search…
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
    1
    shell> mysql -u root -p abc_prod
    Copied!
    2.
    Enable slow query log
    1
    mysql> SET GLOBAL slow_query_log = 'ON';
    Copied!
    3.
    Check path to log file
    1
    mysql> SHOW VARIABLES LIKE 'slow_query_log_file';
    2
    +---------------------+-----------------------------+
    3
    | Variable_name | Value |
    4
    +---------------------+-----------------------------+
    5
    | slow_query_log_file | /var/lib/mysql/db3-slow.log |
    6
    +---------------------+-----------------------------+
    7
    1 row in set (0.00 sec)
    Copied!
    4.
    Change long query time to 5 seconds - default is 10 seconds
    1
    mysql> SET GLOBAL long_query_time = 5;
    Copied!
    5.
    Logout MySQL session then login again
    6.
    Take a small test to ensure slow query log is enable
    1
    mysql> SELECT SLEEP(10);
    Copied!
    7.
    Check sleep on slow query log
    1
    [email protected]:/home/ubuntu:~$ cat /var/lib/mysql/db3-slow.log
    Copied!
    1
    /usr/sbin/mysqld, Version: 5.6.35-log (MySQL Community Server (GPL)). started with:
    2
    Tcp port: 3306 Unix socket: /var/run/mysqld/mysqld.sock
    3
    Time Id Command Argument
    4
    # Time: 170321 7:15:52
    5
    # [email protected]: root[root] @ localhost [] Id: 1160
    6
    # Query_time: 7.000249 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
    7
    SET timestamp=1490080552;
    8
    SELECT SLEEP(7);
    9
    # Time: 170321 7:17:31
    10
    # [email protected]: root[root] @ localhost [] Id: 1161
    11
    # Query_time: 10.000215 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
    12
    use abc_prod;
    13
    SET timestamp=1490080651;
    14
    SELECT SLEEP(10);
    Copied!
Most important:
Using mysqldumpslow tool for summarize slow query log files
1
[email protected]:/home/ubuntu:~$ mysqldumpslow /var/lib/mysql/db3-slow.log
Copied!
1
Reading mysql slow query log from /var/lib/mysql/db3-slow.log
2
Count: 2 Time=8.50s (17s) Lock=0.00s (0s) Rows=1.0 (2), root[root]@localhost
3
SELECT SLEEP(N)
Copied!

How to turn it off?

1
mysql> SET GLOBAL slow_query_log = 'OFF';
Copied!
Last modified 2yr ago