Mysql – Slow Query logging everything

MySQLperformancequeryquery-performance

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

Best Answer

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.

Related Solutions

Mysql – Unexplained InnoDB timeouts

I know this is really late, but you really need to capture the output of SHOW ENGINE INNODB STATUS; during that query to see why it's waiting.

If it happens a lot during a specific time, it would be easy to just grab that output every x seconds and hope you capture it (or perhaps artificially generate the load).

MySQL – Why Are 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 – Unexplained InnoDB timeouts

MySQL – Why Are Slow Queries Not Logging?

UPDATE 2013-03-05 16:36 EST

Related Question