Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Can you give me an example on how to enable MySQL's slow query log?

According to the doc:

As of MySQL 5.1.29, use --slow_query_log[={0|1}] to enable or disable the slow query log, and optionally --slow_query_log_file=file_name to specify a log file name. The --log-slow-queries option is deprecated.

So how do I use that option? Can I put it in my.cnf? An example would be greatly appreciated.

Thank you very much

share|improve this question

2 Answers 2

up vote 4 down vote accepted

Correct.

It's best to put this in the general/global (top) part of your my.cnf file.

There are two things you need to do : (1) enable it, and (2) define the logfile destination.

slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-query.log

Hope this helps.

share|improve this answer
    
Can you also give me an example of how to do it in command line? Thanks –  Continuation Feb 28 '11 at 12:16
    
I'm unsure; It's best to define it outside of editing files / command line. If you have FreeBSD, you can edit rc.conf .. many linux distros (and Solaris, etc) have rc.d files that you can control the server operation with. What OS are you running? –  jamie rishaw Feb 28 '11 at 16:44
    
To answer your Q in a basic form, you'd add the flags to the mysqld_safe script .. Note, also, the underscores (I believe) become dashes.. so, you'd add to mysqld / mysqld "--slow-query-log=1 --slow-query-log-file=/var/log/mysql/whereever.log". –  jamie rishaw Feb 28 '11 at 16:51
1  
You may also want to set the long_query_time value to something suitable to your databases. –  Coops Mar 4 '11 at 9:22

Here is something interesting.

MySQL 5.1/5.5 allows you to convert the slow query log to MyISAM.

Here is how I do it for my clients:

Step 1) Add these line to /etc/my.cnf

log_output=TABLE
slow_query_log
slow_query_log_file=slow-queries.log

Step 2) Restart mysql (service mysql restart)

At this point, the table mysql.slow_log exists but as storage engine CSV.

Step 3) Convert mysql.slow_log to MyISAM and Index the start_time field

SET @old_log_state = @@global.slow_query_log;
SET GLOBAL slow_query_log = 'OFF';
ALTER TABLE mysql.slow_log ENGINE = MyISAM;
ALTER TABLE mysql.slow_log ADD INDEX (start_time);
SET GLOBAL slow_query_log = @old_log_state;

Now you can run SELECT queries against it.

Give it a Try.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.