Thread: Connection pooling
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?
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 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. 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 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.