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

[Tatsuo Ishii]

> 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?

That depends how you set to num_init_children.  The concurrent
(active) connections from Pgpool-II to backend is limited by
num_init_children (= number of pgpool child process).

Maybe you misunderstand how pgpool manages connections to PostgreSQL.

For example, you have num_init_children = 1 and max_pool = 2 following
will happend:

1) user A with Database dA connects to pgpool. Pgpool create connections to db1/db2.
2) user B with Database dB connects to pgpool. it will be in the wait queue since num_init_children = 1.
3) user A disconnects from pgpool. The connection from pgpool to db1/db2 remain.
4) user B with Database dB connects to pgpool. Pgpool create connections to db1/db2.

At this point pgpool has 4 connections to backend (to db1/db2 as A/dA,
to db1/db2 as B/dB). Among this the connection to db1/db2 as B/dB is
the active connection. The connection to db1/db2 as A/dA is the non
active connection.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp