Enable Slow Query Logging for MySQL in DX NetOps Spectrum

Products

• Spectrum
• Network Observability

Issue/Introduction

It may be necessary to enable MySQL Slow Query Logging to troubleshoot or capture 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

All supported DX NetOps Spectrum releases

Resolution

To enable Slow Query Logging 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. Change to the $SPECROOT/mysql/bin directory.
4. Enter the following to log into the MySQL prompt.
   • ./mysql --defaults-file=../my-spectrum.cnf -uroot -p<PASSWD>;
5. Enter the following command at the mysql prompt:
   • SET GLOBAL slow_query_log = 'ON';
6. To disable Slow Query Logging just type:
   • SET GLOBAL slow_query_log = 'OFF';

NOTE: By default, the slow query log will be located in the $SPECROOT/mysql/data directory. The filename will start with the hostname of the system and then "-slow.log".  For example, if the hostname is "MySpectrum", then the slow query log file name will be "MySpectrum-slow.log".

Additional Information

Optional configurations and tests

1. Custom log location and file name.
   • By default Slow Query Log data writes to the $SPECROOT/mysql/data/<hostname>-slow.log.
   • To change the location and file name use the following after enabling the logging.
     • SET GLOBAL slow_query_log_file = '/path_to/filename';
   • Example sending it to /tmp/123456.log file.
     • SET GLOBAL slow_query_log_file = '/tmp/123456.log';
2. Only log queries that take longer than X time (in seconds).
   • The default time is 10 seconds.
     • IE any query running >10 seconds will be logged.
     • If default is acceptable, do not run this command. No need to run it unless setting a time frame lower or higher than the default.
   • This example shows 30 seconds being set. Change the value set to the time frame required in seconds.
     • SET GLOBAL long_query_time = 30;
3. Validate slow query logging is working properly.
   • Run the following sleep command that is 1 second longer than the time set for long_query_time.
     • Using default 10 seconds as an example we'd run this.
       • SELECT SLEEP(11);
   • After the command completes check the slow query log to make sure the query has been logged.