Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
@rocketeerbkw You need to set general_log to 'ON' see this – tiagojco Jun 14 '14 at 8:58

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.

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

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

@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

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

I had the same problem. Solved it after reading this:

In order to enable slow queries logging, you need first to enable general logging.

Just set 'general_log' to 'ON' (in my.cnf)

general_log = 1
share|improve this answer
That question is about the general query log, not the slow query log. Turning on the general query log will not solve this problem. – Ladadadada Jun 14 '14 at 9:40
what i meant was that in order to turn ON slow_query_log, u must ALSO turn on the general_log. Answer edited... – tiagojco Jun 25 '14 at 14:50

Your Answer


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.