CODEWITHSUNDEEP
mysqllogging
Scruffy Paws
Scruffy Paws

Reputation: 1250

How to log slow queries in MySQL 5.0.45

I am trying to turn on logging of slow queries on MySQL v5.0.45 (compile OS: redhat-linus-gnu), but I can't seem to get it working. I tried adding...

long_query_time = 1

to my /etc/my.cnf file as the last option under the [mysqld] section and then I restarted using the command...

sudo /sbin/service mysqld restart --log-slow-queries=/var/log/slowqueries.log

Then I ran programs where I know the SELECT query is taking around 6-8 seconds, but I can't find the log anywhere. Any ideas?

Upvotes: 3

Views: 4888

Answers (2)

mmiddleton
mmiddleton

Reputation: 346

For MySQL 5.0, the variable you want to use in my.cnf is: 

log-slow-queries = /var/log/mylogname.log

You will need to create this file manually and set ownership and group to mysql. You can do this on the command line with: 

touch /var/log/mylogname.log
chown mysql /var/log/mylogname.log
chgrp mysql /var/log/mylogname.log

You can also set the length of time that you consider "long" with: 

long_query_time = 2

You can add queries that are performed without indexes using: 

log-queries-not-using-indexes

But, this might fill up your log really fast.

I found it strange that some variables use underscores and some use hyphens, and it can be different depending on how you set the variables, through my.cnf, or through the command line. Here is a list of all system variables you can set in MySQL 5.0: http://dev.mysql.com/doc/refman/5.0/en/server-options.html

Something else weird is: this documentation says that log-slow-queries is a Boolean value, and when I check my global variables (in mysql, type 'show global variables;'), it shows the value of this as 'ON', not as the file path I set it to. Regardless, this worked for me, and it wrote the file.

Upvotes: 4

daemonofchaos
daemonofchaos

Reputation: 795

In your /etc/my.cnf add log_slow_queries = /var/log/mysql/slowqueries.log and then restart mysql as usual, e.g. sudo service mysqld restart and give that a try.

Upvotes: 0

Related Questions