How can I enable the Slow Query Logging for MySQL

Products

• CA Spectrum

Issue/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.

How can I enable Slow Query Logging in MySQL?

Environment

Any version of Spectrum

Resolution

To enable Slow Query Logging please follow these steps

1 - Log into the system as the user that owns the Spectrum installation

2 - If on Windows, start a bash shell by running "bash -login"

3 - cd to the $SPECROOT/mysql/command and enter the following command to log into mysql:

./mysql --defaults-file=../my-spectrum.cnf -uroot -p<PASSWD>;

4 - Enter the following command at the mysql prompt:

SET GLOBAL slow_query_log = 'ON';

5 - 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;

6 - 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 5.

SELECT SLEEP(31);

7 -  After the command has completed you can check the slow query log to make sure the query has been logged. 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';

8 - To disable Slow Query Logging just type:

SET GLOBAL slow_query_log = 'OFF';