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

[David Kerr]

On Jun 6, 2013, at 7:34 PM, Tatsuo Ishii <ishii at postgresql.org> wrote:

> 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.


I do currently set client_idle_limit to 300 actually because of the JDBC hanging connections
problem. It seems like in a high transaction environment i'd have to set that much lower
and that may be a bit of a performance hit

I think in my current environment Transaction Pooling + Load Balancing would be 
a huge win.