Re: Random slow queries - Mailing list pgsql-performance

From Tom Lane
Subject Re: Random slow queries
Date
Msg-id 29847.1467231631@sss.pgh.pa.us
Whole thread Raw
In response to Re: Random slow queries  (devel.brain99@xoxy.net)
Responses Re: Random slow queries  (devel.brain99@xoxy.net)
List pgsql-performance
devel.brain99@xoxy.net writes:
> As you can see from the logs I posted, it appears the execution plan was
> cached (LOG:  duration: 122006.000 ms  bind cached-1453392550: select....).
> Maybe those aren't processed by auto_explain?

In that, "cached-1453392550" is a statement name given by the client;
you'd know better than we do where it's coming from, but it has no
particular significance to the server.

The real information here is that what is taking 122 seconds is the BIND
step of extended query protocol.  That explains why auto_explain doesn't
notice it; auto_explain only instruments the execution phase.  Typically,
what takes time in the BIND step is planning the query, so it seems like
we have to conclude that something in planning is getting hung up.  That
doesn't get us very much closer to an explanation though :-(.

Don't know if it would be practical for you at all, but if you could
attach to a process that's stuck like this with a debugger and get a stack
trace, that would probably be very informative.

https://wiki.postgresql.org/wiki/Generating_a_stack_trace_of_a_PostgreSQL_backend

            regards, tom lane


pgsql-performance by date:

Previous
From: Jeff Janes
Date:
Subject: Re: pg_xlog dir not getting swept
Next
From: Jeff Janes
Date:
Subject: Re: Random slow queries