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.

Running:
Ubuntu Server 10.04
Mysql 5.1.41

I have the following in /etc/mysql/my.cnf

slow_query_log = 1
slow_query_log_file = /var/log/mysqld/log-slow-queries.log
long_query_time = 1

log file should be writable

drwxrwxrwx  2 mysql mysql    4096 2010-10-20 13:41 mysqld
-rwxrwxrwx  1 mysql mysql    0    2010-10-20 13:41 log-slow-queries.log

Nothing is showing up in the log or in mysql.slow_log table. It looks like it's enabled

mysql> show variables like '%SLOW%';
+---------------------+--------------------------------------+
| Variable_name       | Value                                |
+---------------------+--------------------------------------+
| log_slow_queries    | ON                                   |
| slow_launch_time    | 2                                    |
| slow_query_log      | ON                                   |
| slow_query_log_file | /var/log/mysqld/log-slow-queries.log |
+---------------------+--------------------------------------+
4 rows in set (0.00 sec)

Does this mean I have no slow queries? I tried running select sleep(3); but that doesn't show up.

Is there a query I can run that will show up in the log if everything is configured correctly?

share|improve this question
    
Does the long query logger definitely count idle time? It may only count CPU and I/O time, ignoring the duration of that sleep. –  David Spillett Oct 20 '10 at 21:57
    
Good question, I don't know the answer. I was only guessing when I tried using sleep(). –  rocketeerbkw Oct 20 '10 at 22:12
    
I haven't been able to enable the slow query log on any server I've tried (3 prod, 1 dev, 1 brand new just to test this) including mysql versions 5.0 and 5.1. I assume this feature is broken, has never and will never work. –  rocketeerbkw Oct 28 '10 at 4:20
    
Did you ever resolve this? I'm having the same problem. –  supertrue Jul 3 '12 at 13:14
add comment

4 Answers

The slow query log consists of all SQL statements that took more than long_query_time seconds to execute and (as of MySQL 5.1.21) required at least min_examined_row_limit rows to be examined.

http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html

What's your min_examined_row_limit set to?

share|improve this answer
    
It's set to zero –  rocketeerbkw Oct 20 '10 at 22:10
add comment

The easiest way to create a long-running query is to use a cartesian join on enough tables to make a big mess. Create a test table (or locate an existing one) containing about 50 entries. Then run

SELECT t4.* FROM testtable t1, testtable t2, testtable t3, testtable t4, testtable t5 limit 312499999,1;

which will sit for a long while, then print a record from testtable and a message like "1 row in set (1 min 31.45 sec)". The limit/offset is to force the server to calculate the first 312499999 rows but only show you one row rather than all 312500000 rows (50^5) because it would probably take a couple of days to print that many lines of data out.

If you don't have exactly 50 lines, set the offset to count(*)^(number of joins)-1. Don't do this to a server that's actively being used (or go for a lower number of rows so it's over in a few seconds rather than bogging down the server for a minute and a half)

share|improve this answer
    
That query takes about 6 seconds to run, and my slow query log is still emtpy –  rocketeerbkw Oct 21 '10 at 19:29
add comment

@David, it does log real time, but it doesn't include the time to grab table locks.

As @Bryan pointed out, the query needs to examine certain number of rows in order to be logged. I guess that's why sleep(3) was not logged.

The configuration looks correct. Did you "ls" the right file? I saw you have a "mysqld" in the same directory as "/var/log/mysqld/log-slow-queries.log". By the way, the log file should not have permission as "777" :)

I'm interested to see how the problem gets solved.

share|improve this answer
    
The limit is set to 0. The two file permissions lines I copied were not from the same ls command, just wanted to show what the perms were set to. I know 777 is not correct, but didn't know if mysql was not able to write to log file. –  rocketeerbkw Oct 28 '10 at 4:17
add comment

It's possible mysql is logging to a table. Try running

select * from mysql.slow_log;
share|improve this answer
    
mysql.slow_log doesn't exist –  rocketeerbkw Mar 23 '11 at 16:38
add comment

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.