Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How can I enable MySQL's slow query log without restarting MySQL?

Tags:

mysql

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?

like image 809
mmattax Avatar asked Mar 08 '10 18:03

mmattax


People also ask

How do I slow down a MySQL query?

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.

How do you slow a query log?

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.

How do I find the slow query log path in MySQL?

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.


2 Answers

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.

like image 123
Ian Gregory Avatar answered Oct 13 '22 06:10

Ian Gregory


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.

like image 44
Nitin Avatar answered Oct 13 '22 04:10

Nitin