<div dir="ltr">Perfect, thanks for your reply!<div><br></div><div>How can I validate that connection pooling is actually happening then? Something to verify that I've configured it properly?</div><div><br></div><div>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.</div><div><br></div><div>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.</div><div><br></div><div>Thanks</div><div><br></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Wed, Jul 1, 2020 at 3:46 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">> Hi Guys,<br>
> <br>
> When I list the output of SHOW POOL_POOLS, all the pools have a 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 | 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 | 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 | 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 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>
<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>
</blockquote></div>