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

[Cal Heldenbrand]

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
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20141107/ba191ccf/attachment.html>