Connection pooling

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

On 9/7/07, Max Zorloff <zorloff@gmail.com> wrote:

On Fri, 07 Sep 2007 10:58:36 +0400, Marko Kreen <markokr@gmail.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

On Fri, 07 Sep 2007 10:58:36 +0400, Marko Kreen <markokr@gmail.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.

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.

On 9/7/07, Max Zorloff <zorloff@gmail.com> wrote:

On Fri, 07 Sep 2007 10:58:36 +0400, Marko Kreen <markokr@gmail.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.

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.

It does if you're running without pg_pconnect (i.e. using regular
pg_connects) and if the script doesn't crash the apache/php backend
it's running in.

Are you using pg_pconnect and / or having crashing apache backends?

On Sat, 08 Sep 2007 19:28:52 +0400, Scott Marlowe
<scott.marlowe@gmail.com> wrote:

On 9/7/07, Max Zorloff <zorloff@gmail.com> wrote:

On Fri, 07 Sep 2007 10:58:36 +0400, Marko Kreen <markokr@gmail.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.

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.

It does if you're running without pg_pconnect (i.e. using regular
pg_connects) and if the script doesn't crash the apache/php backend
it's running in.

Are you using pg_pconnect and / or having crashing apache backends?

I specifically turn off pconnects and I don't think I crash backends,
never saw mistakes of that type.