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

Matthew Tice mjtice at gmail.com
Thu Jul 2 23:50:09 JST 2020


Hi, thanks again for your reply Tatsuo.

These are all concurrent connections.

If I reduce 'num_init_children' to something smaller (e.g. 20) I do see the
connection pools being reused ('pool_counter' changes).

After playing around with pgbench, various settings like 'num_init_children',
'max_pool' and reading through your reply I'm starting to think that
pgPool-II may not do what I'm trying to achieve.  Specifically where
application-1 .. application-N are able to open perhaps hundreds of
connections to pgPool-II but, because of connection pooling, pgPool-II
would only open one or two connections (per application) to the backend
database.

[application-1] ->(hundreds of connections) -> [pgPool-II] -> (few
connections) -> [db1]
[application-2] ->(hundreds of connections) -> [pgPool-II] -> (few
connections) -> [db2]
...
[application-300] ->(hundreds of connections) -> [pgPool-II] -> (few
connections) -> [db300]

Am I correct in that the above is not possible currently?

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

> > Perfect, thanks for your reply!
>
> You are welcome!
>
> > How can I validate that connection pooling is actually happening then?
> > Something to verify that I've configured it properly?
>
> I usually use pgbench for this purpose. pgbench's -C option helps.
> For example, I have 4 num_init_children with two backend system.
>
> pgbench -n -C -p 11000 -c 4 -t 10 -S test
> transaction type: <builtin: select only>
> scaling factor: 1
> query mode: simple
> number of clients: 4
> number of threads: 1
> number of transactions per client: 10
> number of transactions actually processed: 40/40
> latency average = 2.029 ms
> tps = 1971.232620 (including connections establishing)
> tps = 2573.647207 (excluding connections establishing)
>
> You can see some pgpool process have pool_counter something around 10.
>
> test=# show pool_pools;
> -[ RECORD 1 ]---+--------------------
> pool_pid        | 2782
> start_time      | 2020-07-02 09:37:43
> pool_id         | 0
> backend_id      | 0
> database        | test
> username        | t-ishii
> create_time     | 2020-07-02 09:38:00
> majorversion    | 3
> minorversion    | 0
> pool_counter    | 12
> pool_backendpid | 2804
> pool_connected  | 0
> -[ RECORD 2 ]---+--------------------
> pool_pid        | 2782
> start_time      | 2020-07-02 09:37:43
> pool_id         | 0
> backend_id      | 1
> database        | test
> username        | t-ishii
> create_time     | 2020-07-02 09:38:00
> majorversion    | 3
> minorversion    | 0
> pool_counter    | 12
> pool_backendpid | 2805
> pool_connected  | 0
> -[ RECORD 3 ]---+--------------------
> pool_pid        | 2782
> start_time      | 2020-07-02 09:37:43
> pool_id         | 1
> backend_id      | 0
> database        |
> username        |
> create_time     |
> majorversion    | 0
> minorversion    | 0
> pool_counter    | 0
> pool_backendpid | 0
> pool_connected  | 0
> -[ RECORD 4 ]---+--------------------
> pool_pid        | 2782
> start_time      | 2020-07-02 09:37:43
> pool_id         | 1
> backend_id      | 1
> database        |
> username        |
> create_time     |
> majorversion    | 0
> minorversion    | 0
> pool_counter    | 0
> pool_backendpid | 0
> pool_connected  | 0
> -[ RECORD 5 ]---+--------------------
> pool_pid        | 2782
> start_time      | 2020-07-02 09:37:43
> pool_id         | 2
> backend_id      | 0
> database        |
> username        |
> create_time     |
> majorversion    | 0
> minorversion    | 0
> pool_counter    | 0
> pool_backendpid | 0
> pool_connected  | 0
> -[ RECORD 6 ]---+--------------------
> pool_pid        | 2782
> start_time      | 2020-07-02 09:37:43
> pool_id         | 2
> backend_id      | 1
> database        |
> username        |
> create_time     |
> majorversion    | 0
> minorversion    | 0
> pool_counter    | 0
> pool_backendpid | 0
> pool_connected  | 0
> -[ RECORD 7 ]---+--------------------
> pool_pid        | 2782
> start_time      | 2020-07-02 09:37:43
> pool_id         | 3
> backend_id      | 0
> database        |
> username        |
> create_time     |
> majorversion    | 0
> minorversion    | 0
> pool_counter    | 0
> pool_backendpid | 0
> pool_connected  | 0
> -[ RECORD 8 ]---+--------------------
> pool_pid        | 2782
> start_time      | 2020-07-02 09:37:43
> pool_id         | 3
> backend_id      | 1
> database        |
> username        |
> create_time     |
> majorversion    | 0
> minorversion    | 0
> pool_counter    | 0
> pool_backendpid | 0
> pool_connected  | 0
> -[ RECORD 9 ]---+--------------------
> pool_pid        | 2783
> start_time      | 2020-07-02 09:37:43
> pool_id         | 0
> backend_id      | 0
> database        | test
> username        | t-ishii
> create_time     | 2020-07-02 09:38:00
> majorversion    | 3
> minorversion    | 0
> pool_counter    | 10
> pool_backendpid | 2810
> pool_connected  | 0
> -[ RECORD 10 ]--+--------------------
> pool_pid        | 2783
> start_time      | 2020-07-02 09:37:43
> pool_id         | 0
> backend_id      | 1
> database        | test
> username        | t-ishii
> create_time     | 2020-07-02 09:38:00
> majorversion    | 3
> minorversion    | 0
> pool_counter    | 10
> pool_backendpid | 2811
> pool_connected  | 0
> -[ RECORD 11 ]--+--------------------
> pool_pid        | 2783
> start_time      | 2020-07-02 09:37:43
> pool_id         | 1
> backend_id      | 0
> database        |
> username        |
> create_time     |
> majorversion    | 0
> minorversion    | 0
> pool_counter    | 0
> pool_backendpid | 0
> pool_connected  | 0
> -[ RECORD 12 ]--+--------------------
> pool_pid        | 2783
> start_time      | 2020-07-02 09:37:43
> pool_id         | 1
> backend_id      | 1
> database        |
> username        |
> create_time     |
> majorversion    | 0
> minorversion    | 0
> pool_counter    | 0
> pool_backendpid | 0
> pool_connected  | 0
> -[ RECORD 13 ]--+--------------------
> pool_pid        | 2783
> start_time      | 2020-07-02 09:37:43
> pool_id         | 2
> backend_id      | 0
> database        |
> username        |
> create_time     |
> majorversion    | 0
> minorversion    | 0
> pool_counter    | 0
> pool_backendpid | 0
> pool_connected  | 0
> -[ RECORD 14 ]--+--------------------
> pool_pid        | 2783
> start_time      | 2020-07-02 09:37:43
> pool_id         | 2
> backend_id      | 1
> database        |
> username        |
> create_time     |
> majorversion    | 0
> minorversion    | 0
> pool_counter    | 0
> pool_backendpid | 0
> pool_connected  | 0
> -[ RECORD 15 ]--+--------------------
> pool_pid        | 2783
> start_time      | 2020-07-02 09:37:43
> pool_id         | 3
> backend_id      | 0
> database        |
> username        |
> create_time     |
> majorversion    | 0
> minorversion    | 0
> pool_counter    | 0
> pool_backendpid | 0
> pool_connected  | 0
> -[ RECORD 16 ]--+--------------------
> pool_pid        | 2783
> start_time      | 2020-07-02 09:37:43
> pool_id         | 3
> backend_id      | 1
> database        |
> username        |
> create_time     |
> majorversion    | 0
> minorversion    | 0
> pool_counter    | 0
> pool_backendpid | 0
> pool_connected  | 0
> -[ RECORD 17 ]--+--------------------
> pool_pid        | 2784
> start_time      | 2020-07-02 09:37:43
> pool_id         | 0
> backend_id      | 0
> database        | test
> username        | t-ishii
> create_time     | 2020-07-02 09:38:07
> majorversion    | 3
> minorversion    | 0
> pool_counter    | 1
> pool_backendpid | 2820
> pool_connected  | 1
> -[ RECORD 18 ]--+--------------------
> pool_pid        | 2784
> start_time      | 2020-07-02 09:37:43
> pool_id         | 0
> backend_id      | 1
> database        | test
> username        | t-ishii
> create_time     | 2020-07-02 09:38:07
> majorversion    | 3
> minorversion    | 0
> pool_counter    | 1
> pool_backendpid | 2819
> pool_connected  | 1
> -[ RECORD 19 ]--+--------------------
> pool_pid        | 2784
> start_time      | 2020-07-02 09:37:43
> pool_id         | 1
> backend_id      | 0
> database        |
> username        |
> create_time     |
> majorversion    | 0
> minorversion    | 0
> pool_counter    | 0
> pool_backendpid | 0
> pool_connected  | 0
> -[ RECORD 20 ]--+--------------------
> pool_pid        | 2784
> start_time      | 2020-07-02 09:37:43
> pool_id         | 1
> backend_id      | 1
> database        |
> username        |
> create_time     |
> majorversion    | 0
> minorversion    | 0
> pool_counter    | 0
> pool_backendpid | 0
> pool_connected  | 0
> -[ RECORD 21 ]--+--------------------
> pool_pid        | 2784
> start_time      | 2020-07-02 09:37:43
> pool_id         | 2
> backend_id      | 0
> database        |
> username        |
> create_time     |
> majorversion    | 0
> minorversion    | 0
> pool_counter    | 0
> pool_backendpid | 0
> pool_connected  | 0
> -[ RECORD 22 ]--+--------------------
> pool_pid        | 2784
> start_time      | 2020-07-02 09:37:43
> pool_id         | 2
> backend_id      | 1
> database        |
> username        |
> create_time     |
> majorversion    | 0
> minorversion    | 0
> pool_counter    | 0
> pool_backendpid | 0
> pool_connected  | 0
> -[ RECORD 23 ]--+--------------------
> pool_pid        | 2784
> start_time      | 2020-07-02 09:37:43
> pool_id         | 3
> backend_id      | 0
> database        |
> username        |
> create_time     |
> majorversion    | 0
> minorversion    | 0
> pool_counter    | 0
> pool_backendpid | 0
> pool_connected  | 0
> -[ RECORD 24 ]--+--------------------
> pool_pid        | 2784
> start_time      | 2020-07-02 09:37:43
> pool_id         | 3
> backend_id      | 1
> database        |
> username        |
> create_time     |
> majorversion    | 0
> minorversion    | 0
> pool_counter    | 0
> pool_backendpid | 0
> pool_connected  | 0
> -[ RECORD 25 ]--+--------------------
> pool_pid        | 2785
> start_time      | 2020-07-02 09:37:43
> pool_id         | 0
> backend_id      | 0
> database        | test
> username        | t-ishii
> create_time     | 2020-07-02 09:38:00
> majorversion    | 3
> minorversion    | 0
> pool_counter    | 10
> pool_backendpid | 2809
> pool_connected  | 0
> -[ RECORD 26 ]--+--------------------
> pool_pid        | 2785
> start_time      | 2020-07-02 09:37:43
> pool_id         | 0
> backend_id      | 1
> database        | test
> username        | t-ishii
> create_time     | 2020-07-02 09:38:00
> majorversion    | 3
> minorversion    | 0
> pool_counter    | 10
> pool_backendpid | 2808
> pool_connected  | 0
> -[ RECORD 27 ]--+--------------------
> pool_pid        | 2785
> start_time      | 2020-07-02 09:37:43
> pool_id         | 1
> backend_id      | 0
> database        |
> username        |
> create_time     |
> majorversion    | 0
> minorversion    | 0
> pool_counter    | 0
> pool_backendpid | 0
> pool_connected  | 0
> -[ RECORD 28 ]--+--------------------
> pool_pid        | 2785
> start_time      | 2020-07-02 09:37:43
> pool_id         | 1
> backend_id      | 1
> database        |
> username        |
> create_time     |
> majorversion    | 0
> minorversion    | 0
> pool_counter    | 0
> pool_backendpid | 0
> pool_connected  | 0
> -[ RECORD 29 ]--+--------------------
> pool_pid        | 2785
> start_time      | 2020-07-02 09:37:43
> pool_id         | 2
> backend_id      | 0
> database        |
> username        |
> create_time     |
> majorversion    | 0
> minorversion    | 0
> pool_counter    | 0
> pool_backendpid | 0
> pool_connected  | 0
> -[ RECORD 30 ]--+--------------------
> pool_pid        | 2785
> start_time      | 2020-07-02 09:37:43
> pool_id         | 2
> backend_id      | 1
> database        |
> username        |
> create_time     |
> majorversion    | 0
> minorversion    | 0
> pool_counter    | 0
> pool_backendpid | 0
> pool_connected  | 0
> -[ RECORD 31 ]--+--------------------
> pool_pid        | 2785
> start_time      | 2020-07-02 09:37:43
> pool_id         | 3
> backend_id      | 0
> database        |
> username        |
> create_time     |
> majorversion    | 0
> minorversion    | 0
> pool_counter    | 0
> pool_backendpid | 0
> pool_connected  | 0
> -[ RECORD 32 ]--+--------------------
> pool_pid        | 2785
> start_time      | 2020-07-02 09:37:43
> pool_id         | 3
> backend_id      | 1
> database        |
> username        |
> create_time     |
> majorversion    | 0
> minorversion    | 0
> pool_counter    | 0
> pool_backendpid | 0
> pool_connected  | 0
>
> test=#
>
> > 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.
>
> So your hope is handling several hundred databases/applications
> concurrently (and limit up to 20 connections for database/application
> pair). If they really concurrently access PostgreSQL, you need
> several hundred max_connections. There's no way to avoid this. On the
> other hand, if they do not access database really concurrently, for
> example application A accesses DB for 5 minutes then disconnect, and
> then application B accesses DB for 5 minutes, you can reduce the
> number of concurrent connections. In this case you should:
>
> - disable connection cache in Pgpool-II so that a connection to
>   backend can be used by different applications.
>
> - limit num_init_children to 20*(number of concurrent access to DB)
>
> - set same number to max_connections of PostgreSQL
>
> - set max_pool to 1
>
> > 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/20200702/6091a1cd/attachment-0001.html>


More information about the pgpool-general mailing list