7

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?

5
  • Does the long query logger definitely count idle time? It may only count CPU and I/O time, ignoring the duration of that sleep. Oct 20, 2010 at 21:57
  • Good question, I don't know the answer. I was only guessing when I tried using sleep(). Oct 20, 2010 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. Oct 28, 2010 at 4:20
  • Did you ever resolve this? I'm having the same problem.
    – supertrue
    Jul 3, 2012 at 13:14
  • @rocketeerbkw You need to set general_log to 'ON' see this stackoverflow.com/a/11861305/686304
    – tiagojco
    Jun 14, 2014 at 8:58

9 Answers 9

5

I had the same problem. Solved it after reading this: https://stackoverflow.com/a/11861305/686304

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
4
  • 3
    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, 2014 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, 2014 at 14:50
  • He is correct and this answer should get points !
    – Dani
    Dec 11, 2017 at 20:01
  • 4
    @tiagojco Either EDIT your incorrect Answer (which you could do) OR DELETE it to avoid misleading people toward a BAD result. Full disk exposure, please. Turning ON the GENERAL LOG has NOTHING to do with usefulness of Slow Query Log. Jan 23, 2019 at 18:57
3

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?

1
3

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

select * from mysql.slow_log;
2
  • mysql.slow_log doesn't exist Mar 23, 2011 at 16:38
  • I'm on mysql 5.6 and it does exist.
    – funder7
    Oct 2, 2021 at 19:32
1

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)

2
  • 1
    That query takes about 6 seconds to run, and my slow query log is still emtpy Oct 21, 2010 at 19:29
  • 3
    Currently it's easier to use SELECT SLEEP(2) for a query to take 2 seconds.
    – FantomX1
    Sep 16, 2020 at 2:25
1

I had the same problem and checked the path /var/log/mysql/ error.log file where MySQL stores the errors. I saw the log saying about permission was denied to access the file.

2022-07-05  9:45:10 0 [ERROR] mysqld: File '/home/debian/slow_log_file' not found (Errcode: 13 "Permission denied")
2022-07-05  9:45:10 0 [ERROR] Could not use /home/debian/slow_log_file for logging (error 13). Turning logging off for the whole duration of the MariaDB server process. To turn it on again: fix the cause, shutdown the MariaDB server and restart it.

Changing the permission of the file or moving the file to some location where MySQL user can access resolved my problem.

1

I'm adding an answer after all this time, mostly as I encountered the same problem, and this is still one of the top hits on google. Hopefully I'll save someone else the grief of working out how to fix this.

I'm using the Percona build, but this seems to be a universal issue.

I tracked the problem down to slow_query_log_use_global_control which was initially empty. After setting it to all then everything started working ok.

My current working config looks like:

SET GLOBAL slow_query_log_use_global_control = all;
SET GLOBAL general_log = OFF;
SET GLOBAL slow_query_log = OFF;
SET GLOBAL log_queries_not_using_indexes = OFF;
SET GLOBAL slow_query_log_file='/tmp/slow_query';
SET GLOBAL long_query_time = 0.001;
SET GLOBAL slow_query_log_always_write_time = 0.001;
SET GLOBAL slow_query_log = ON;
0

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

1
  • 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. Oct 28, 2010 at 4:17
0

I ran into a similar issue recently - MySQL 5.6 on RDS. After digging into the config for a while to make sure it was going to the right place, I noticed the following error in the error logs:

23780 [ERROR] Could not open /rdsdbdata/log/slowquery/mysql-slowquery.log for logging (error 2). Turning logging off for the whole duration of the MySQL server process. To turn it on again: fix the cause, shutdown the MySQL server and restart it.

So if nothing is showing up, make sure that MySQL is able to write to the slow log, and hasn't hit an error that caused it to turn off logging entirely.

0

My experience is such, you should be pretty clear the file is accessible, for my case not only I needed to change owner to mysql:mysql , the file had to be also under the /tmp directory (yeah you hear right the /tmp directory due to /etc/init.d/apparmor linux security), chmod alone 0777 would not help. Also it was not enough doing that without restarting the mysql server. The change was still corrected just after the restart, no matter that file privileges are changed immediately.

You can check the origin of some settings by this query

SELECT t1.*, VARIABLE_VALUE FROM performance_schema.variables_info t1 JOIN performance_schema.global_variables t2 ON t2.VARIABLE_NAME=t1.VARIABLE_NAME WHERE t1.VARIABLE_NAME LIKE 'slow_query_log';

alongside your own well-known show variables like '%SLOW%';

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .