How to enable slow query logs in MySQL without restarting it?

Products

• Spectrum
• Network Observability

Issue/Introduction

For troubleshooting purposes, it can be necessary to discover what queries in MySQL are running slow.

Resolution

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

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

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

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

- Enter the following command at the mysql prompt:

mysql> SET GLOBAL long_query_time = X;    (where X is the number of seconds to consider a query as slow)

mysql> SET GLOBAL slow_query_log_file = '/tmp/slow_queries.log';  (if linux)

or

mysql> SET GLOBAL slow_query_log_file = 'C:\\tmp\\slow_queries.log';  (if windows)

PS: All the queries that take more than the X seconds configured above, will be logged into this file.

mysql> SET GLOBAL slow_query_log = 'ON'; (as soon as you run this command, you should see the 'slow_queries.log' file being created on the path configured above)

- Once you have collected the desired data, you can disable the log generation by running the following command:

mysql> SET GLOBAL slow_query_log = 'OFF';

For Portal:

Login to the portal system as the install owner.

Type: mysql -uroot -p

Enter the password set during installation (Broadcom does not know your password here)

Perform the same MySQL commands above.