For troubleshooting purposes, it can be necessary to discover what queries in MySQL are running slow.
Release : 10.3.x, 10.4.x
Component : Spectrum
Here are the steps:
1- Connect on MySQL:
Linux:
- Go to $SPECROOT/mysql/bin directory;
- Run the following command:
./mysql --defaults-file=../my-spectrum.cnf -uroot -proot
Windows:
- open a bash -login window;
- Run the following command:
./mysql -uroot -proot
2- Once connected, run the following commands:
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)
3- 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';