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
 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
 [email protected]:/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
 # [email protected]: 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
 # [email protected]: 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
[email protected]:/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';

InnoDB - innodb_file_per_table parameter

MySQL - export large tables

Last modified 3yr ago