Re: How to investiage slow insert problem

(@Jeff, sorry I sent this message only to you by mistake, sending to the
list now...)

On Tue, Aug 20, 2013 at 9:24 PM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:

> On Mon, Aug 19, 2013 at 10:30 PM, Rural Hunter <ruralhunter(at)gmail(dot)com>
> wrote:
> > 于 2013/8/20 12:34, Jeff Janes 写道:
> >
>
> > > How long had they been hanging there? It makes a big difference
> whether
> > > there are several hanging there at one moment, but a few milliseconds
> later
> > > there are several different ones, versus the same few that hang around
> of
> > > many seconds or minutes at a time.
> >
> > The hanging connections never disappear until I restart pgbouncer. It's
> like
> > this, At minute 1, 3 connections left. At minute 2, another 3 left,
> total 6.
> > Another minute, another 3 left, total 9....till the limit reaches.
>
> OK, that certainly does sound like network problems and not disk
> contention. But what I don't see is why it would be listed as "active" in
> pg_stat_activity. If it is blocked on a network connection, I would think
> it would show 'idle'.

IIRC, the "state" column will show if the query on "query" column is really
running or not (by not I mean, it is "idle[ in transaction]"), the column
"waiting" is the one that we should look at to see if the backend is really
blocked, which is the case if waiting is true. If it is true, then we
should check at pg_locks to see who is blocking it, [1] and [2] has good
queries for that.

[1] http://wiki.postgresql.org/wiki/Lock_Monitoring
[2] http://wiki.postgresql.org/wiki/Lock_dependency_information

Regards,
--
Matheus de Oliveira
Analista de Banco de Dados
Dextra Sistemas - MPS.Br nível F!
www.dextra.com.br/postgres