[pgpool-general: 1836] 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: Thu, 6 Jun 2013 19:50:30 -0700
Message-ID: <91EAA4A5-2312-40A7-9377-B4BE50663375 at mr-paradox.net>

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

Ok. So in other word, pgbouncer in *session mode* does not help you. I
just want to confirm your requirement.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp