Enable slow query log on MySQL

[hieuhtr]

❓ 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

   shell> mysql -u root -p abc_prod

2. Enable slow query log

   mysql> SET GLOBAL slow_query_log = 'ON';

3. Check path to log file

   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

   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

   mysql> SELECT SLEEP(10);

7. Check sleep on slow query log

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

   /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

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?

mysql> SET GLOBAL slow_query_log = 'OFF';