[pgpool-general: 6547] Re: pgPool II with Hibernate

[Tatsuo Ishii]

> I want to configure pgpool only as "load balancer" for the SQL queries.
> This is my scenario:
> - one application server with tomcat. For each server, there is a single
> user that makes the queries. The user is for example "userApplication". I
> want to use the application connection pooling (c3p0).
> - a single node with pgpool installed configured to send R/W queries to the
> master and R only queries to the slaves.
> - a postgresql-cluster (postgresql-10) with one master node and one slave.
> 
> I've used the default configuration for pgpool (pgpool.conf.sample)
> changing only:
> *listen_addresses*
> *port*
> 
> *backend_hostnameX*
> *backend_portX*
> *connection_cache = off*

You'd better to use pgpool.conf.sample-stream since you are
configuring streaming replication cluster.

> So, in my understanding, setting connection_cache to off will disable the
> connection pooling. But there are too many idle connections on postgres
> opened by pgpool, so it seems that pgpool still open num_init_children.

Because from Pgpool-II's point of view, your client (in your case
tomcat's connection pooling) keeps on connecting to Pgpool-II. Until the
client disconnects to Pgpool-II, it cannot close a connection to
PostgreSQL.

> Moreover, if I correctly understand connections with the same user name,
> database, protocol version go to the same child process, so in my case, I'm
> using only one of the child process and the others aren't used.

No. See the FAQ entry which explains why:
https://pgpool.net/mediawiki/index.php/FAQ#Why_max_connection_must_satisfy_this_formula_max_connection_.3E.3D_.28num_init_children_.2A_max_pool.29_and_not_max_connection_.3E.3D_num_init_children.3F

> Is there a way to use pgPool to load balancing sql queries from the same
> user to a different child process?

No.

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