2

I have a mysql instance that is logging slow queries. I have it set to 1 sec, but I am getting back lots of these:(example) Query_time: 0.000237 Lock_time: 0.000064 Rows_sent: 0 Rows_examined: 109

On another server I get these: Query_time: 0 Lock_time: 0 Rows_sent: 0 Rows_examined: 0

Either way they both seem to be logging other queries...or am I reading something wrong?

set-variable = log-slow-queries="/tmp/mysql-slow-query.log"
   set-variable = long_query_time=1
3

If you're using mysql 5.1.21 or newer, you can change the behavior of logging slow queries with the min_examined_row_length

Set it to something like 5000 or so, and keep your log_queries_not_using_indexes turned on.

  • that's a +1 for an interesting approach I never explored before. – RolandoMySQLDBA Jul 30 '11 at 11:46
  • @derekdowney Do you mean min_examined_row_limit ? If so, please correct. Thanks – Wilson Hauck Nov 13 '17 at 11:41
2

You should raise the long_query_time to 10 (the default value)

MySQL 5.1 allows sub-second granularity of the long_query_time, which may explain why you see decmial notation for the first slow query log display.

You may also want to check and make sure that log_queries_not_using_indexes is not enabled. Otherwise, any old query not using indexes will bypass the long_query_time setting and get recorded in the slow log.

CAVEAT

set-variable is deprecated in MySQL 5.1

This is from MySQL 5.1.37

  -O, --set-variable=name
                      Change the value of a variable. Please note that this
                      option is deprecated;you can set variables directly with
                      --variable-name=value.

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.