Re: Connection pooling

On 9/7/07, Max Zorloff <zorloff(at)gmail(dot)com> wrote:
> On Fri, 07 Sep 2007 10:58:36 +0400, Marko Kreen <markokr(at)gmail(dot)com> wrote:
> >> The pgpool (I tried 3.1, 3.4 and pgpool-II 1.2) works fine but has the
> >> following problem - after some time it
> >> just "hangs", and if I try to connect to it with psql it just hangs
> >> indefinitely. After restart
> >> it works fine again. I turned off persistent connections in php so it's
> >> not that. Does anybody
> >> have the same problem?
> >
> > All symptoms point to the same problem - your app fails to
> > release server connections for reuse.
> >
> > If the problem is that PHP fails to disconnect connection,
> > although the transaction is finished, you could run pgbouncer
> > in more relaxed mode - pool_mode=transaction. Also setting
> > client_idle_timeout to something may help debugging.
>
> > If the problem is uncommitted transactions, you could set
> > query_timeout to some small number (1-3) to see where
> > errors appear.
> >
> > Both timeouts are not something I would put into productions
> > config, so the code should be fixed still...
>
> pgbouncer does not have this problem, only pgpool does.
> pgbouncer has the problem of being very slow.

pgbouncer gets slower because there are decreasing amount
of usable server connections. it will hang too in the end.

You can study SHOW POOLS; command output on pgbouncer
console, that should give good picture whether pgbouncer
itself is slow or clients are waiting on server connection.

> i thought php released connections at the end of script?
> and also if i had this problem pgpool would hang in a few seconds
> because the server has some load.

There have been some reports in this list that PHP connection
handling is not very robust. You can work around that by
serring pool_mode = transaction. Then only open transactions
can cause problems.

--
marko