How can I enable the Slow Query Logging for MySQL

Document ID : KB000100882
Last Modified Date : 12/06/2018
Show Technical Document Details
Introduction:
It may be necessary to enable MySQL Slow Query Logging in order to troubleshoot or capture the queries when MySQL is taking a long time to complete.  These steps will show you how to enable and remove slow query logging.
Question:
How can I enable Slow Query Logging in MySQL?
Environment:
Spectrum 10.0
Spectrum 10.1
Spectrum 10.2
Answer:
To enable Slow Query Logging please follow these steps

1. Login to MySQL from $SPECROOT/mysql/bin

Windows: 
./mysql -uroot -proot;

Linux / Solaris (as Spectrum Admin user)
./mysql --defaults-file=../my-spectrum.cnf -uroot -proot;

2. Enable Slow Query Logging

SET GLOBAL slow_query_log = 'ON';

3. Decide on the time that queries will be logged when time is reached.  (default 10 seconds), for this example I choose 30 Seconds.
SET GLOBAL long_query_time = 30;

4.  You can test this to make sure slow query is working properly by adding a sleep command that is 1 second longer then the time decided in step 3.
SELECT SLEEP(31);
After the command has completed you can check the slow query log to make sure the query has been logged.

5. By default the Slow Query Log is located in $SPECROOT/mysql/data as <hostname>-slow.log but if you want to change the location you can do so by typing:
SET GLOBAL slow_query_log_file = '/path_to/filename';

6. To disable Slow Query Logging just type:
SET GLOBAL slow_query_log = 'OFF';