On Mon, Aug 19, 2013 at 10:30 PM, Rural Hunter <ruralhunter@gmail.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.