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.
How can I enable Slow Query Logging in MySQL?
To enable Slow Query Logging please follow these steps
1. Login to MySQL from $SPECROOT/mysql/bin
./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.
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';