<div dir="ltr"><div>Hi, thanks again for your reply Tatsuo.</div><div><br></div>These are all concurrent connections. <div><br></div><div>If I reduce 'num_init_children' to something smaller (e.g. 20) I do see the connection pools being reused ('<span style="background-color:rgb(247,247,247);color:rgb(0,0,0)">pool_counter' changes).</span></div><div><span style="background-color:rgb(247,247,247);color:rgb(0,0,0)"><br></span></div><div>After playing around with pgbench, various settings like '<span style="color:rgb(0,0,0);font-family:monospace;font-size:medium">num_init_children', 'max_pool'</span> 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.</div><div><br></div><div>[application-1] ->(hundreds of connections) -> [pgPool-II] -> (few connections) -> [db1]</div><div>[application-2] ->(hundreds of connections) -> [pgPool-II] -> (few connections) -> [db2]<br></div><div>...</div><div>[application-300] ->(hundreds of connections) -> [pgPool-II] -> (few connections) -> [db300]<br></div><div><br></div><div>Am I correct in that the above is not possible currently?</div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Wed, Jul 1, 2020 at 8:55 PM Tatsuo Ishii <<a href="mailto:ishii@sraoss.co.jp">ishii@sraoss.co.jp</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">> Perfect, thanks for your reply!<br>
<br>
You are welcome!<br>
<br>
> How can I validate that connection pooling is actually happening then?<br>
> Something to verify that I've configured it properly?<br>
<br>
I usually use pgbench for this purpose. pgbench's -C option helps.<br>
For example, I have 4 num_init_children with two backend system.<br>
<br>
pgbench -n -C -p 11000 -c 4 -t 10 -S test<br>
transaction type: <builtin: select only><br>
scaling factor: 1<br>
query mode: simple<br>
number of clients: 4<br>
number of threads: 1<br>
number of transactions per client: 10<br>
number of transactions actually processed: 40/40<br>
latency average = 2.029 ms<br>
tps = 1971.232620 (including connections establishing)<br>
tps = 2573.647207 (excluding connections establishing)<br>
<br>
You can see some pgpool process have pool_counter something around 10.<br>
<br>
test=# show pool_pools;<br>
-[ RECORD 1 ]---+--------------------<br>
pool_pid | 2782<br>
start_time | 2020-07-02 09:37:43<br>
pool_id | 0<br>
backend_id | 0<br>
database | test<br>
username | t-ishii<br>
create_time | 2020-07-02 09:38:00<br>
majorversion | 3<br>
minorversion | 0<br>
pool_counter | 12<br>
pool_backendpid | 2804<br>
pool_connected | 0<br>
-[ RECORD 2 ]---+--------------------<br>
pool_pid | 2782<br>
start_time | 2020-07-02 09:37:43<br>
pool_id | 0<br>
backend_id | 1<br>
database | test<br>
username | t-ishii<br>
create_time | 2020-07-02 09:38:00<br>
majorversion | 3<br>
minorversion | 0<br>
pool_counter | 12<br>
pool_backendpid | 2805<br>
pool_connected | 0<br>
-[ RECORD 3 ]---+--------------------<br>
pool_pid | 2782<br>
start_time | 2020-07-02 09:37:43<br>
pool_id | 1<br>
backend_id | 0<br>
database | <br>
username | <br>
create_time | <br>
majorversion | 0<br>
minorversion | 0<br>
pool_counter | 0<br>
pool_backendpid | 0<br>
pool_connected | 0<br>
-[ RECORD 4 ]---+--------------------<br>
pool_pid | 2782<br>
start_time | 2020-07-02 09:37:43<br>
pool_id | 1<br>
backend_id | 1<br>
database | <br>
username | <br>
create_time | <br>
majorversion | 0<br>
minorversion | 0<br>
pool_counter | 0<br>
pool_backendpid | 0<br>
pool_connected | 0<br>
-[ RECORD 5 ]---+--------------------<br>
pool_pid | 2782<br>
start_time | 2020-07-02 09:37:43<br>
pool_id | 2<br>
backend_id | 0<br>
database | <br>
username | <br>
create_time | <br>
majorversion | 0<br>
minorversion | 0<br>
pool_counter | 0<br>
pool_backendpid | 0<br>
pool_connected | 0<br>
-[ RECORD 6 ]---+--------------------<br>
pool_pid | 2782<br>
start_time | 2020-07-02 09:37:43<br>
pool_id | 2<br>
backend_id | 1<br>
database | <br>
username | <br>
create_time | <br>
majorversion | 0<br>
minorversion | 0<br>
pool_counter | 0<br>
pool_backendpid | 0<br>
pool_connected | 0<br>
-[ RECORD 7 ]---+--------------------<br>
pool_pid | 2782<br>
start_time | 2020-07-02 09:37:43<br>
pool_id | 3<br>
backend_id | 0<br>
database | <br>
username | <br>
create_time | <br>
majorversion | 0<br>
minorversion | 0<br>
pool_counter | 0<br>
pool_backendpid | 0<br>
pool_connected | 0<br>
-[ RECORD 8 ]---+--------------------<br>
pool_pid | 2782<br>
start_time | 2020-07-02 09:37:43<br>
pool_id | 3<br>
backend_id | 1<br>
database | <br>
username | <br>
create_time | <br>
majorversion | 0<br>
minorversion | 0<br>
pool_counter | 0<br>
pool_backendpid | 0<br>
pool_connected | 0<br>
-[ RECORD 9 ]---+--------------------<br>
pool_pid | 2783<br>
start_time | 2020-07-02 09:37:43<br>
pool_id | 0<br>
backend_id | 0<br>
database | test<br>
username | t-ishii<br>
create_time | 2020-07-02 09:38:00<br>
majorversion | 3<br>
minorversion | 0<br>
pool_counter | 10<br>
pool_backendpid | 2810<br>
pool_connected | 0<br>
-[ RECORD 10 ]--+--------------------<br>
pool_pid | 2783<br>
start_time | 2020-07-02 09:37:43<br>
pool_id | 0<br>
backend_id | 1<br>
database | test<br>
username | t-ishii<br>
create_time | 2020-07-02 09:38:00<br>
majorversion | 3<br>
minorversion | 0<br>
pool_counter | 10<br>
pool_backendpid | 2811<br>
pool_connected | 0<br>
-[ RECORD 11 ]--+--------------------<br>
pool_pid | 2783<br>
start_time | 2020-07-02 09:37:43<br>
pool_id | 1<br>
backend_id | 0<br>
database | <br>
username | <br>
create_time | <br>
majorversion | 0<br>
minorversion | 0<br>
pool_counter | 0<br>
pool_backendpid | 0<br>
pool_connected | 0<br>
-[ RECORD 12 ]--+--------------------<br>
pool_pid | 2783<br>
start_time | 2020-07-02 09:37:43<br>
pool_id | 1<br>
backend_id | 1<br>
database | <br>
username | <br>
create_time | <br>
majorversion | 0<br>
minorversion | 0<br>
pool_counter | 0<br>
pool_backendpid | 0<br>
pool_connected | 0<br>
-[ RECORD 13 ]--+--------------------<br>
pool_pid | 2783<br>
start_time | 2020-07-02 09:37:43<br>
pool_id | 2<br>
backend_id | 0<br>
database | <br>
username | <br>
create_time | <br>
majorversion | 0<br>
minorversion | 0<br>
pool_counter | 0<br>
pool_backendpid | 0<br>
pool_connected | 0<br>
-[ RECORD 14 ]--+--------------------<br>
pool_pid | 2783<br>
start_time | 2020-07-02 09:37:43<br>
pool_id | 2<br>
backend_id | 1<br>
database | <br>
username | <br>
create_time | <br>
majorversion | 0<br>
minorversion | 0<br>
pool_counter | 0<br>
pool_backendpid | 0<br>
pool_connected | 0<br>
-[ RECORD 15 ]--+--------------------<br>
pool_pid | 2783<br>
start_time | 2020-07-02 09:37:43<br>
pool_id | 3<br>
backend_id | 0<br>
database | <br>
username | <br>
create_time | <br>
majorversion | 0<br>
minorversion | 0<br>
pool_counter | 0<br>
pool_backendpid | 0<br>
pool_connected | 0<br>
-[ RECORD 16 ]--+--------------------<br>
pool_pid | 2783<br>
start_time | 2020-07-02 09:37:43<br>
pool_id | 3<br>
backend_id | 1<br>
database | <br>
username | <br>
create_time | <br>
majorversion | 0<br>
minorversion | 0<br>
pool_counter | 0<br>
pool_backendpid | 0<br>
pool_connected | 0<br>
-[ RECORD 17 ]--+--------------------<br>
pool_pid | 2784<br>
start_time | 2020-07-02 09:37:43<br>
pool_id | 0<br>
backend_id | 0<br>
database | test<br>
username | t-ishii<br>
create_time | 2020-07-02 09:38:07<br>
majorversion | 3<br>
minorversion | 0<br>
pool_counter | 1<br>
pool_backendpid | 2820<br>
pool_connected | 1<br>
-[ RECORD 18 ]--+--------------------<br>
pool_pid | 2784<br>
start_time | 2020-07-02 09:37:43<br>
pool_id | 0<br>
backend_id | 1<br>
database | test<br>
username | t-ishii<br>
create_time | 2020-07-02 09:38:07<br>
majorversion | 3<br>
minorversion | 0<br>
pool_counter | 1<br>
pool_backendpid | 2819<br>
pool_connected | 1<br>
-[ RECORD 19 ]--+--------------------<br>
pool_pid | 2784<br>
start_time | 2020-07-02 09:37:43<br>
pool_id | 1<br>
backend_id | 0<br>
database | <br>
username | <br>
create_time | <br>
majorversion | 0<br>
minorversion | 0<br>
pool_counter | 0<br>
pool_backendpid | 0<br>
pool_connected | 0<br>
-[ RECORD 20 ]--+--------------------<br>
pool_pid | 2784<br>
start_time | 2020-07-02 09:37:43<br>
pool_id | 1<br>
backend_id | 1<br>
database | <br>
username | <br>
create_time | <br>
majorversion | 0<br>
minorversion | 0<br>
pool_counter | 0<br>
pool_backendpid | 0<br>
pool_connected | 0<br>
-[ RECORD 21 ]--+--------------------<br>
pool_pid | 2784<br>
start_time | 2020-07-02 09:37:43<br>
pool_id | 2<br>
backend_id | 0<br>
database | <br>
username | <br>
create_time | <br>
majorversion | 0<br>
minorversion | 0<br>
pool_counter | 0<br>
pool_backendpid | 0<br>
pool_connected | 0<br>
-[ RECORD 22 ]--+--------------------<br>
pool_pid | 2784<br>
start_time | 2020-07-02 09:37:43<br>
pool_id | 2<br>
backend_id | 1<br>
database | <br>
username | <br>
create_time | <br>
majorversion | 0<br>
minorversion | 0<br>
pool_counter | 0<br>
pool_backendpid | 0<br>
pool_connected | 0<br>
-[ RECORD 23 ]--+--------------------<br>
pool_pid | 2784<br>
start_time | 2020-07-02 09:37:43<br>
pool_id | 3<br>
backend_id | 0<br>
database | <br>
username | <br>
create_time | <br>
majorversion | 0<br>
minorversion | 0<br>
pool_counter | 0<br>
pool_backendpid | 0<br>
pool_connected | 0<br>
-[ RECORD 24 ]--+--------------------<br>
pool_pid | 2784<br>
start_time | 2020-07-02 09:37:43<br>
pool_id | 3<br>
backend_id | 1<br>
database | <br>
username | <br>
create_time | <br>
majorversion | 0<br>
minorversion | 0<br>
pool_counter | 0<br>
pool_backendpid | 0<br>
pool_connected | 0<br>
-[ RECORD 25 ]--+--------------------<br>
pool_pid | 2785<br>
start_time | 2020-07-02 09:37:43<br>
pool_id | 0<br>
backend_id | 0<br>
database | test<br>
username | t-ishii<br>
create_time | 2020-07-02 09:38:00<br>
majorversion | 3<br>
minorversion | 0<br>
pool_counter | 10<br>
pool_backendpid | 2809<br>
pool_connected | 0<br>
-[ RECORD 26 ]--+--------------------<br>
pool_pid | 2785<br>
start_time | 2020-07-02 09:37:43<br>
pool_id | 0<br>
backend_id | 1<br>
database | test<br>
username | t-ishii<br>
create_time | 2020-07-02 09:38:00<br>
majorversion | 3<br>
minorversion | 0<br>
pool_counter | 10<br>
pool_backendpid | 2808<br>
pool_connected | 0<br>
-[ RECORD 27 ]--+--------------------<br>
pool_pid | 2785<br>
start_time | 2020-07-02 09:37:43<br>
pool_id | 1<br>
backend_id | 0<br>
database | <br>
username | <br>
create_time | <br>
majorversion | 0<br>
minorversion | 0<br>
pool_counter | 0<br>
pool_backendpid | 0<br>
pool_connected | 0<br>
-[ RECORD 28 ]--+--------------------<br>
pool_pid | 2785<br>
start_time | 2020-07-02 09:37:43<br>
pool_id | 1<br>
backend_id | 1<br>
database | <br>
username | <br>
create_time | <br>
majorversion | 0<br>
minorversion | 0<br>
pool_counter | 0<br>
pool_backendpid | 0<br>
pool_connected | 0<br>
-[ RECORD 29 ]--+--------------------<br>
pool_pid | 2785<br>
start_time | 2020-07-02 09:37:43<br>
pool_id | 2<br>
backend_id | 0<br>
database | <br>
username | <br>
create_time | <br>
majorversion | 0<br>
minorversion | 0<br>
pool_counter | 0<br>
pool_backendpid | 0<br>
pool_connected | 0<br>
-[ RECORD 30 ]--+--------------------<br>
pool_pid | 2785<br>
start_time | 2020-07-02 09:37:43<br>
pool_id | 2<br>
backend_id | 1<br>
database | <br>
username | <br>
create_time | <br>
majorversion | 0<br>
minorversion | 0<br>
pool_counter | 0<br>
pool_backendpid | 0<br>
pool_connected | 0<br>
-[ RECORD 31 ]--+--------------------<br>
pool_pid | 2785<br>
start_time | 2020-07-02 09:37:43<br>
pool_id | 3<br>
backend_id | 0<br>
database | <br>
username | <br>
create_time | <br>
majorversion | 0<br>
minorversion | 0<br>
pool_counter | 0<br>
pool_backendpid | 0<br>
pool_connected | 0<br>
-[ RECORD 32 ]--+--------------------<br>
pool_pid | 2785<br>
start_time | 2020-07-02 09:37:43<br>
pool_id | 3<br>
backend_id | 1<br>
database | <br>
username | <br>
create_time | <br>
majorversion | 0<br>
minorversion | 0<br>
pool_counter | 0<br>
pool_backendpid | 0<br>
pool_connected | 0<br>
<br>
test=# <br>
<br>
> Our Postgres instance is home to several hundred databases uniquely used by<br>
> several hundred applications. For example, application1 only uses<br>
> database1, application2 only uses database2, and so on.<br>
> <br>
> Because of this multitenancy we've restricted the number of connections to<br>
> each database to 20. This worked when we were running dozens of databases<br>
> but it hasn't scaled well and I was hoping that pgpool would resolve this.<br>
> However, I do have a few applications that are still hitting that 20<br>
> connection limit in the database.<br>
<br>
So your hope is handling several hundred databases/applications<br>
concurrently (and limit up to 20 connections for database/application<br>
pair). If they really concurrently access PostgreSQL, you need<br>
several hundred max_connections. There's no way to avoid this. On the<br>
other hand, if they do not access database really concurrently, for<br>
example application A accesses DB for 5 minutes then disconnect, and<br>
then application B accesses DB for 5 minutes, you can reduce the<br>
number of concurrent connections. In this case you should:<br>
<br>
- disable connection cache in Pgpool-II so that a connection to<br>
backend can be used by different applications.<br>
<br>
- limit num_init_children to 20*(number of concurrent access to DB)<br>
<br>
- set same number to max_connections of PostgreSQL<br>
<br>
- set max_pool to 1<br>
<br>
> Thanks<br>
> <br>
> <br>
> On Wed, Jul 1, 2020 at 3:46 PM Tatsuo Ishii <<a href="mailto:ishii@sraoss.co.jp" target="_blank">ishii@sraoss.co.jp</a>> wrote:<br>
> <br>
>> > Hi Guys,<br>
>> ><br>
>> > When I list the output of SHOW POOL_POOLS, all the pools have a<br>
>> pool_counter=1<br>
>> > and a distinct pool_backendpid. To me this means that even though I have<br>
>> > the same users connecting, with the same major/minor, connection pools<br>
>> > aren't being reused. Is that correct? Here's a snippet (I have three<br>
>> > backends):<br>
>> ><br>
>> > 141204 | 2020-07-01 11:04:21 | 0 | 0 |<br>
>> mobile-search-bff<br>
>> > | v-token-mo-mobile-s-yAjmcIzpEvK | 2020-07-01 12:16:19 | 3<br>
>> > | 0 | 1 | 12047 | 1<br>
>> > 141204 | 2020-07-01 11:04:21 | 0 | 1 |<br>
>> mobile-search-bff<br>
>> > | v-token-mo-mobile-s-yAjmcIzpEvK | 2020-07-01 12:16:19 | 3<br>
>> > | 0 | 1 | 60420 | 1<br>
>> > 141204 | 2020-07-01 11:04:21 | 0 | 2 |<br>
>> mobile-search-bff<br>
>> > | v-token-mo-mobile-s-yAjmcIzpEvK | 2020-07-01 12:16:19 | 3<br>
>> > | 0 | 1 | 137228 | 1<br>
>> [snip]<br>
>> > The only thing I can think of is that it may have something to do with<br>
>> the<br>
>> > user names (these are Vault-generated users).<br>
>> ><br>
>> > I do have the connection cache parameter on:<br>
>> ><br>
>> > connection_cache on<br>
>> ><br>
>> > I'm using PgPool-II v. 4.1.1.<br>
>><br>
>> Yes, that's an expected behavior. The OS feels free to assign a<br>
>> pool_pid for a client connection to any of pre-forked pool_pid. The OS<br>
>> does not care whether the pool_pid already has a connection pool to<br>
>> backend. So until all pool_pid have same connection pool (same<br>
>> user/database pair), it may be possible that pool_counter remains 1.<br>
>><br>
>> The FAQ explains this in a different way.<br>
>><br>
>> <a href="https://pgpool.net/mediawiki/index.php/FAQ#Is_connection_pool_cache_shared_among_pgpool_process.3F" rel="noreferrer" target="_blank">https://pgpool.net/mediawiki/index.php/FAQ#Is_connection_pool_cache_shared_among_pgpool_process.3F</a><br>
>><br>
>> Best regards,<br>
>> --<br>
>> Tatsuo Ishii<br>
>> SRA OSS, Inc. Japan<br>
>> English: <a href="http://www.sraoss.co.jp/index_en.php" rel="noreferrer" target="_blank">http://www.sraoss.co.jp/index_en.php</a><br>
>> Japanese:<a href="http://www.sraoss.co.jp" rel="noreferrer" target="_blank">http://www.sraoss.co.jp</a><br>
>><br>
</blockquote></div>