[pgpool-general: 7115] Re: Pooling doesn't appear to be working.
Tatsuo Ishii
ishii at sraoss.co.jp
Thu Jul 2 11:55:45 JST 2020
> 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
>>
More information about the pgpool-general
mailing list