I followed the instructions here: http://crazytoon.com/2007/07/23/mysql-changing-runtime-variables-with-out-restarting-mysql-server/ but that seems to only set the threshold.
Do I need to do anything else like set the filepath?
According to MySQL's docs
If no file_name value is given for --log-slow-queries, the default name is host_name-slow.log. The server creates the file in the data directory unless an absolute path name is given to specify a different directory.
Running
SHOW VARIABLES
doesn't indicate any log file path and I don't see any slow query log file on my server...
EDIT
Looks like I'm using server version 5.0.77, so I needed to do:
SET GLOBAL log_slow_queries = 1;
but I get: ERROR 1238 (HY000): Variable 'log_slow_queries' is a read only variable
I assume I'm going to need to restart the server and have log_slow_queries set in my config?
MySQL has a built-in slow query log. To use it, open the my. cnf file and set the slow_query_log variable to "On." Set long_query_time to the number of seconds that a query should take to be considered slow, say 0.2. Set slow_query_log_file to the path where you want to save the file.
To disable or enable the slow query log or change the log file name at runtime, use the global slow_query_log and slow_query_log_file system variables. Set slow_query_log to 0 to disable the log or to 1 to enable it. Set slow_query_log_file to specify the name of the log file.
By default, the slow query log file is located at /var/lib/mysql/hostname-slow. log. We can also set up another location as shown in listing 03 using the slow_query_log_file parameter. We can also indicate to log queries not using indexes, as shown in the listing 04.
Try SET GLOBAL slow_query_log = 'ON';
and perhaps FLUSH LOGS;
This assumes you are using MySQL 5.1 or later. If you are using an earlier version, you'll need to restart the server. This is documented in the MySQL Manual. You can configure the log either in the config file or on the command line.
For slow queries on version < 5.1, the following configuration worked for me:
log_slow_queries=/var/log/mysql/slow-query.log long_query_time=20 log_queries_not_using_indexes=YES
Also note to place it under [mysqld]
part of the config file and restart mysqld
.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With