0

what timestamps are used for calculating the query_time parameter in the mysql slow query log? Can't find any definition of that parameter. Only thing i found is

The time to acquire the initial locks is not counted as execution time.

from: http://dev.mysql.com/doc/refman/5.0/en/slow-query-log.html

I'm asking this question because i want to know if the time, given by the 'request_time' in slow log, includes waiting times in queues (if there are some).

If there are some queues, is there a possibility to log the current waiting queue length when a new query is going to be executed (preferably in the slow log).

If there are no queues, how does mysql handle incoming queries if all threads are currently executing some query? Update: Remain those queries in the TCP-Buffer till they can be executed by a thread?

Links to further reading are welcome.

Regards Korbinian

Korbinian Grimme
  • 13
  • 5
  • [Profile](http://dev.mysql.com/doc/refman/5.5/en/show-profile.html) the query to get the specific times, including waiting for locks. – Marcus Adams Feb 12 '15 at 20:58
  • I can not see that profiling shows how long a query has to wait in some queue (if there is any). I'm running some analysis on the such logfiles so i want to know what time the parameter exaclty shows. – Korbinian Grimme Feb 12 '15 at 21:19

1 Answers1

0

There are no queues like you describe.

When the query starts, the connection is dedicated to running it. It may be blocked by any of a large number of "mutexes" because of various things shared between connections. As the query runs, it may have to wait for I/O to read blocks from disk.

The query time in the slowlog is essentially the clock-on-the-wall time for the query. A query that normally takes 1 second may take 3 seconds if there are other connections hanging onto mutexes or doing lots of I/O.

Caching will let a query run faster. The first time you run a query (after restarting mysql), it will run slow because of all the uncached I/O.

If you are using Engine MyISAM, the "lock_time" will often be significant because of MyISAM's table locking. InnoDB rarely shows more than a fraction of a millisecond in lock_time.

In older version of MySQL, the timer was similar to TIMESTAMP with a 1-second resolution. In newer versions, it has much higher resolution.

The time at the start of a slowlog entry is the timestamp of when the query started. You may notice that the slowlog entries are not always in order according to that. This is simply because a slowlog entry is not written to the file until the query finishes.

OK, there is a queue -- but it is almost never needed. And it is at the time of establishing the connection. If there are already max_connection connections, then see back_log. I repeat, that is not a queue for executing queries.

Rick James
  • 135,179
  • 13
  • 127
  • 222