5

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.

2 Answers 2

5

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.

4
  • Can you also give me an example of how to do it in command line? Thanks Feb 28, 2011 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? Feb 28, 2011 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". Feb 28, 2011 at 16:51
  • 1
    You may also want to set the long_query_time value to something suitable to your databases.
    – Coops
    Mar 4, 2011 at 9:22
5

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .