[pgpool-general: 4494] Re: Why so many backend connections?

[Muhammad Usama]

On Thu, Feb 25, 2016 at 7:18 AM, Brandon Ridler <brandonr at motiga.com> wrote:

> Hello,
>
> I've scoured the site and the interwebs with out being able to find
> information in regards to what I'm seeing and am hoping I can be pointed in
> the right direction, or given an explanation as to why it appears every
> connection to pgpool is resulting in a connection to every backend I have.
> My general setup when compared to a default is:
>
> 1.  I have 3 backends, all weight 1.
> 2.  I have master/slave mode enabled with stream.
> 3.  I have load_balancing on.
> 4.  I have connection_cache off.
> 5.  I have connection_life_time set to 60.
> 6.  I have watchdog enabled and configured.
>
> I'm using 3.5.0 pgpool2 running on an ubuntu 14.0.4 inside a docker
> container.  I also tried 3.3.2 from the ubuntu apt repos.
>
> It doesn't matter if it's our custom app, an established app like
> ejabberd, or me using psql to connect to it, it will always make
> connections to all backends listed.  I'd like to know if that is expected
> behavior but haven't found anything in the docs to confirm or deny this
> other than a reference to when your making transactions.
>

> Assuming the above is correct, I tried to use the connection_life_time
> value.  Thinking that if the front-end connection to pgpool is idle, and
> thus the backend is idle, that at least pgpool would close the backend and
> re-establish it if anything came down from the apps.
>

Yes It is expected by pgpool-II to make connections with all configured
backend nodes And for the second part, the connection_life_time only
disconnects the idle cached backend connections. While the backend
connection which is in use by the front-end even if the front-end is idle
is not considered as idle by pgpool-II. To disconnect the idle frontend
connections you can set the client_idle_limit config parameter.

Can you please let me know if I'm misunderstanding the intent of pgpool
> here or if there's a configuration that I may be overlooking.  I thought
> when I originally set up pgpool a month ago that I saw like 300 connection
> to pgpool and only 50-60 connections to each of the backends.
>

When the connection cache is enabled on pgpool-II, then pgpool-II does not
close the backend connection even when the client application disconnects.
Each connection is identified by database_name and user_name values of the
connection, So when the client connection request arrives to the pgpool-II
child and that child already have a cached connection with the same
credentials(same database_name and user_name pair), then no new backed
connection is opened by the pgpool-II while pgpool-II hooks the incoming
client connection with that already cached backend connection. Only when
the database_name and user_name pair of incoming client connection does not
exist in the connection cache pgpool-II opens a new backend connection.
The number of backend connection cached by a pgpool-II child can be
configured by the *max_pool* config paramete. And theoretically PostgreSQL
server can have up to  (max_pool*num_init_children) opened connection when
the connection cache is enabled, even when no client is connected to
pgpool-II

Regards
Muhammad Usama


>
> Regards,
> Brandon
>
> _______________________________________________
> pgpool-general mailing list
> pgpool-general at pgpool.net
> http://www.pgpool.net/mailman/listinfo/pgpool-general
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20160225/5cf769d3/attachment-0001.html>