MySQL slow log logs every query instead of just slow queries

MySQLperformanceslow-log

I have the following MySQL configuration for logging the slow queries:

    [mysqld]
    ### Logging options
    general_log_file = /data/mysql-log/mysql_general.log
    log-error = /data/mysql-log/mysql_error.log
    slow_query_log_file = /data/mysql-log/mysql_slow.log
    log-warnings = 1
    log-queries-not-using-indexes = ON
    log-slow-verbosity = full
    long_query_time = 2
    log-slow-admin-statements = 1
    log-slow-slave-statements = 1
    general-log = OFF
    slow-query-log = ON

However, I see the following in my slow log file:
[

To my knowledge, the Query_time in the log file denotes the time query took to run, but it defers the log time mentioned in the config file.

Anyone here to help me explain as to why this is happening?

Best Answer

Maybe your mysqld got it's long_query_time value from another source. What happens if you run:

select @@long_query_time;

Note that I would consider setting the long_query_time to 0 to be good practice, particularly on Innodb databases. Consider the case where query A takes 1 second and runs a thousand times a day vs query B taking 20 seconds and run once a day; you'll get more benefit from tuning query A.

Related Solutions

Mysql – Enabling “log_queries_not_using_indexes” disables “long_query_time”

That is expected behavior. Refer to the docs online, but in summary:

long_query_time is the threshold for query execution time beyond which it is logged. Any queries taking longer than the threshold are logged, regardless of whether they use an index or not.
log_queries_not_using_indexes tells MySQL to additionally log all queries that do not use an index to limit the number of rows scanned. Logging on this condition happens regardless of execution time.

Hope that helps explain what you are seeing. It seems like you probably just want long_query_time and not log_queries_not_using_indexes if your goal is to only capture queries that take longer than a particular threshold.

Doc links: http://dev.mysql.com/doc/refman/5.5/en/server-options.html#option_mysqld_log-queries-not-using-indexes

http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_long_query_time

Mysql – Slow Queries Not Logging

I think I got the answer:

You need to put those options under the [mysqld] section

[mysqld]
slow-query-log=1
slow-query-log-file=/var/logs/my.slow.log
long_query_time=1

and restart mysql

UPDATE 2013-03-05 16:36 EST

I don't know why this is still happening, but please try this:

service mysql stop
rm -f /var/logs/my.slow.log
touch /var/logs/my.slow.log
chown mysql:mysql /var/logs/my.slow.log
service mysql start

then run SELECT SLEEP(10); and see if it lands in /var/logs/my.slow.log

Best Answer

Related Solutions

Mysql – Enabling “log_queries_not_using_indexes” disables “long_query_time”

Mysql – Slow Queries Not Logging

UPDATE 2013-03-05 16:36 EST

Related Question