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.

We have a MySQL 5.1.52 Percona Server 11.6 instance that suddenly started logging every single query to the slow query log. The long_query_time configuration is set to 1, yet, suddenly we're seeing every single query (e.g. just saw one that took 0.000563s!). As a result, our log files are growing at an insane pace. We just had to truncate a 180G slow query log file.

I tried setting the long_query_time variable to a really large number to see if it stopped altogether (1000000), but same result.

show global variables like 'general_log%';
+------------------+--------------------------+
| Variable_name    | Value                    |
+------------------+--------------------------+
| general_log      | OFF                      |
| general_log_file | /usr2/mysql/data/db4.log |
+------------------+--------------------------+
2 rows in set (0.00 sec)

show global variables like 'slow_query_log%';
+---------------------------------------+-------------------------------+
| Variable_name                         | Value                         |
+---------------------------------------+-------------------------------+
| slow_query_log                        | ON                            |
| slow_query_log_file                   | /usr2/mysql/data/db4-slow.log |
| slow_query_log_microseconds_timestamp | OFF                           |
+---------------------------------------+-------------------------------+
3 rows in set (0.00 sec)

show global variables like 'long%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
1 row in set (0.00 sec)
share|improve this question
add comment

1 Answer

up vote 3 down vote accepted

It sounds like log_queries_not_using_indexes is enabled.

Check it by doing:

mysql> show global variables like 'log_queries%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | ON    |
+-------------------------------+-------+

Turn it off with:

mysql> set global log_queries_not_using_indexes = 'off';
Query OK, 0 rows affected (0.00 sec)
share|improve this answer
    
That's it, thank you! setting the variable to off did it. –  Blanka Aug 31 '12 at 21:49
    
I recommend you turn it on and use EXPLAIN to find out why the query doesn't use index. –  quanta Sep 1 '12 at 5:29
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.