[pgpool-general: 3265] Re: Backend selection algorithm

[Tatsuo Ishii]

Yes.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

> Thank you Tatsuo for the info.  If I set connection_cache = false, does
> that turn off *all* connection pooling features?
> 
> --Cal
> 
> On Wed, Nov 5, 2014 at 4:35 PM, Tatsuo Ishii <ishii at postgresql.org> wrote:
> 
>> > Hi everyone,
>> >
>> > In playing with some timeout settings with some real-world live traffic,
>> I
>> > noticed that pgpool keeps a lot of server-side idle connections open.  I
>> > started with a child_life_time of 30 seconds, and it quickly consumed up
>> > all of my num_init_children setting (32) on the postgres server.  I'd
>> like
>> > to minimize the number of connections left open (and idle) on the
>> postgres
>> > server.
>>
>> Yes, that's the effect of connection pooling of pgpool-II. If you
>> don't like this, you can turn it off by setting connection_cache off.
>>
>> > When I dropped the child_life_time down to 5 seconds, it reduced the idle
>> > connections significantly.
>>
>> Yes, becuase each pgpool-II child is responsible for keeping the
>> connection pooling in their process space.
>>
>> > While that effectively solves my problem, I'm
>> > wondering what algorithm is used to select a server pool slot when a new
>> > client connects.  As I was watching it, it seems like an LRU algorithm,
>> > picking the server side slot with the largest idle time.
>>
>> No. pgpool-II employes pre-fork model, which means multiple child
>> process are forked and are waiting for connect(2) request comes from
>> client. If the request arrives to the port pgpool-II is listening on,
>> the kernel selects one of the child process and let it process. I
>> don't know what algorithm is used within the kernel process. Maybe
>> it's URL, maybe not.
>>
>> Best regards,
>> --
>> Tatsuo Ishii
>> SRA OSS, Inc. Japan
>> English: http://www.sraoss.co.jp/index_en.php
>> Japanese:http://www.sraoss.co.jp
>>
>> > If that's the
>> > case, I think I would prefer it to pick the server side slot with the
>> > *smallest* idle time.  The pool should then shrink to the minimal number
>> of
>> > server-side connection slots required to handle the current client-side
>> > load.  Then to account for burst activity, the idle time could be upped
>> to
>> > keep a small amount of extra slots for burst capacity.
>> >
>> > The way it seems now, I have to shorten the idle timeout to something
>> very
>> > short in order to keep from overrunning my connection limits on the
>> > postgres server.
>> >
>> > Does any of this seem correct, or am I way off base here?
>> >
>> > Thank you!
>> >
>> > --Cal
>>