[pgpool-general: 1834] Re: PgPool vs any modern programming language.

[Tatsuo Ishii]

From: David Kerr <web at mr-paradox.net>
Subject: Re: [pgpool-general: 1828] PgPool vs any modern programming language.
Date: Wed, 5 Jun 2013 19:12:31 -0700
Message-ID: <FF354E40-3566-48F5-822B-FD169D800EC3 at mr-paradox.net>

> 
> On Jun 5, 2013, at 6:24 PM, Tatsuo Ishii <ishii at postgresql.org> wrote:
> 
>>> All Rails, Django and many Java (c3po) implementations have client side connection pools.
>>> 
>>> I don't believe that they can be disabled. You can set them to 1 / (thread|process)
>>> but that's still many per server.
>>> 
>>> This means that for every Rails process there is one of these sitting on PgPool at 
>>> application startup:
>>> 
>>> pgpool   16099 15924  0 00:36 ?        00:00:00 pgpool: app app 192.168.10.19(40509) idle
>>> pgpool   16100 15924  0 00:36 ?        00:00:00 pgpool: app app 192.168.14.27(57314) idle
>>> pgpool   16101 15924  0 00:36 ?        00:00:00 pgpool: app app 192.168.10.218(37869) idle
>>> pgpool   16102 15924  0 00:36 ?        00:00:00 pgpool: app app 192.168.10.48(37278) idle
>>> pgpool   16103 15924  0 00:36 ?        00:00:00 pgpool: app app 192.168.10.106(53541) idle
>>> 
>>> If a single app sever has a pool size of 10.  And i have 10 servers.
>>> 
>>> That's 100 _persistent_ connections to PgPool. (and hence the DB)
>>> 
>>> To accomidate that I need 100 initial children and 100 max_connections on the postgres side.
>>> 
>>> That sort of defeats the purpose of pooling. 
>>> 
>>> Ideally, I want PgPool to protect my database from overzealous application deployments
>>> while still giving them all a chance to serve requests.
>>> 
>>> Is this possible?
>>> 
>>> How are other people handling this?
>> 
>> Does pgbouncer's "transaction pooling" help? Or those middlewares
>> require session based pooling like pgpool?
> 
> Hmm. I had various levels of success with pgbouncer / transaction pooling with Java.
> I haven't tried it yet with Rails. Disabling prepared transactions was difficult in the
> java world. it may be easier in the ruby world.
> 
> of course then I don't get the load balancing of PgPool which is pretty impactful.

I think you can set client_idle_limit to other than 0, which will kill
an idle conection client to pgpool, then it gives a change other
client connects to pgpool. Thus you can lower the number of initial
children and max_connection. Downside of this, the client pooler may
think that is a trouble and stop working, rather than creating a new
connection to pgpool.

BTW, does pgbouncer handle your requirement better than pgpool(besides
connection pooling)? If so, pgpool maight be able to learn something
from it.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp