On 9/6/07, Max Zorloff <zorloff@gmail.com> wrote:
> Hello. I'm using Apache + PHP + Postgres for my project. I've tried the
> two poolers people
> usually recommend here - pgbouncer and pgpool.
>
> I have a problem with pgbouncer - under the load the query execution
> becomes ~10 times slower
> than it should be - basically to test it, I connect with psql
> (establishing connection becomes
> somewhat slow under load) and use \timing to measure execution time. The
> basic query of
> "select * from aaa where id = 1" runs 10 times slower than it should. If I
> connect with psql
> directly to postgres, the execution time is acceptable. Does anyone know
> what is the problem?
>
> 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...
--
marko