[pgpool-general: 7114] Re: Pooling doesn't appear to be working.

[Matthew Tice]

Perfect, thanks for your reply!

How can I validate that connection pooling is actually happening then?
Something to verify that I've configured it properly?

Our Postgres instance is home to several hundred databases uniquely used by
several hundred applications.  For example, application1 only uses
database1, application2 only uses database2, and so on.

Because of this multitenancy we've restricted the number of connections to
each database to 20.  This worked when we were running dozens of databases
but it hasn't scaled well and I was hoping that pgpool would resolve this.
However, I do have a few applications that are still hitting that 20
connection limit in the database.

Thanks


On Wed, Jul 1, 2020 at 3:46 PM Tatsuo Ishii <ishii at sraoss.co.jp> wrote:

> > Hi Guys,
> >
> > When I list the output of SHOW POOL_POOLS, all the pools have a
> pool_counter=1
> > and a distinct pool_backendpid.  To me this means that even though I have
> > the same users connecting, with the same major/minor, connection pools
> > aren't being reused.  Is that correct?  Here's a snippet (I have three
> > backends):
> >
> >  141204   | 2020-07-01 11:04:21 | 0       | 0          |
> mobile-search-bff
> >               | v-token-mo-mobile-s-yAjmcIzpEvK | 2020-07-01 12:16:19 | 3
> >          | 0            | 1            | 12047           | 1
> >  141204   | 2020-07-01 11:04:21 | 0       | 1          |
> mobile-search-bff
> >               | v-token-mo-mobile-s-yAjmcIzpEvK | 2020-07-01 12:16:19 | 3
> >          | 0            | 1            | 60420           | 1
> >  141204   | 2020-07-01 11:04:21 | 0       | 2          |
> mobile-search-bff
> >               | v-token-mo-mobile-s-yAjmcIzpEvK | 2020-07-01 12:16:19 | 3
> >          | 0            | 1            | 137228          | 1
> [snip]
> > The only thing I can think of is that it may have something to do with
> the
> > user names (these are Vault-generated users).
> >
> > I do have the connection cache parameter on:
> >
> > connection_cache on
> >
> > I'm using PgPool-II v. 4.1.1.
>
> Yes, that's an expected behavior. The OS feels free to assign a
> pool_pid for a client connection to any of pre-forked pool_pid. The OS
> does not care whether the pool_pid already has a connection pool to
> backend. So until all pool_pid have same connection pool (same
> user/database pair), it may be possible that pool_counter remains 1.
>
> The FAQ explains this in a different way.
>
> https://pgpool.net/mediawiki/index.php/FAQ#Is_connection_pool_cache_shared_among_pgpool_process.3F
>
> Best regards,
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_en.php
> Japanese:http://www.sraoss.co.jp
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20200701/4bf85a30/attachment-0001.html>