MySQL Performance: MySQL => How to set slow query log

How to set slow query log [message #1413]

Wed, 13 June 2007 02:31

julian
Messages: 13
Registered: March 2007

Junior Member

Hi

I want to set slow query log which consists of all SQL statements that took more than long_query_time seconds to execute.

Now default value is set for long_query_time ie 10. Show variables shows

log_slow_queries - OFF

long_query_time - 10

I want to enable log_slow_queries and set long_query_time =1

I found in mysql manual to set slow query log as follows in my.cnf

mysqld]
set-variable=long_query_time=1
log-slow-queries=/var/log/mysql/log-slow-queries.log

You must create the file manually and change owners this way:

mkdir /var/log/mysql
touch /var/log/mysql/log-slow-queries.log
chown mysql.mysql -R /var/log/mysql

Even after changing this in my.cnf, the show Global variables shows as

log_slow_queries - OFF

long_query_time - 10

It looks like same before and the new value not set.

In my linux server, my.cnf settings are

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
set-variable=long_query_time=1
log-slow-queries=/var/log/mysql/log-slow-queries.log

# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

~
~
"/etc/my.cnf" 21L, 454C

Any one say how to find slow log queries. I'm using RHEL 5 and MySQL 5.0.22

thanks

Report message to a moderator