Home / server / Questions / 241113
Accepted
Continuation
Asked: 2011-03-01 03:54:51 +0800 CST

MySQL: how to enable Slow Query Log?

  • 772

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.

mysql

2 Answers

  1. Best Answer

    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.

    • 5

  2. 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.

    • 5