3

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: [1]

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?

3

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.

  • Yes @symcbean your point on setting long_query_time to 0 is valid. But most of my queries are unique and would run once. – sameergautam Sep 8 '16 at 11:52
  • 3
    log-slow-queries-not-using-indexes is ON so check the queries and run an explain with it may be its not using any indexes but still runs less than 2 seconds still it will be logged on the slow query log file. – jerichorivera Sep 8 '16 at 12:48
  • Jericho's answer is correct here. – eroomydna Sep 8 '16 at 18:23
  • Yes @jerichorivera, you got that. Queries not using indexes and running under 2 seconds are being logged in the log. Thanks. – sameergautam Sep 9 '16 at 3:27
1

log-queries-not-using-indexes = ON

I suggest that your tables have no useful indexes.

That particular setting is useless, in my opinion.

Notice how "Rows examined" is a lot bigger than "Rows returned". Check the tables, "Rows examined" is probably how many rows there are in the table.

Would you like to present EXPLAIN SELECT ... and SHOW CREATE TABLE so we can discuss indexes. Or, you can read Index Cookbook .

Once you have added some indexes, turn of that setting and lower long_query_time to 1.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.