<div dir="ltr">Just as follow-up:<div>all the idle connections are due to our strategy of id generation (very good explanation of the problem here --> <a href="https://vladmihalcea.com/why-you-should-never-use-the-table-identifier-generator-with-jpa-and-hibernate/">https://vladmihalcea.com/why-you-should-never-use-the-table-identifier-generator-with-jpa-and-hibernate/</a>).</div><div>We changed the strategy using postgres sequences and all the idle connection were gone.</div><div>So it wasn't a problem of pgpool.</div><div><br></div><div>Best regards </div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">Il giorno sab 4 mag 2019 alle ore 23:44 Tatsuo Ishii <<a href="mailto:ishii@sraoss.co.jp">ishii@sraoss.co.jp</a>> ha scritto:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">> I want to configure pgpool only as "load balancer" for the SQL queries.<br>
> This is my scenario:<br>
> - one application server with tomcat. For each server, there is a single<br>
> user that makes the queries. The user is for example "userApplication". I<br>
> want to use the application connection pooling (c3p0).<br>
> - a single node with pgpool installed configured to send R/W queries to the<br>
> master and R only queries to the slaves.<br>
> - a postgresql-cluster (postgresql-10) with one master node and one slave.<br>
> <br>
> I've used the default configuration for pgpool (pgpool.conf.sample)<br>
> changing only:<br>
> *listen_addresses*<br>
> *port*<br>
> <br>
> *backend_hostnameX*<br>
> *backend_portX*<br>
> *connection_cache = off*<br>
<br>
You'd better to use pgpool.conf.sample-stream since you are<br>
configuring streaming replication cluster.<br>
<br>
> So, in my understanding, setting connection_cache to off will disable the<br>
> connection pooling. But there are too many idle connections on postgres<br>
> opened by pgpool, so it seems that pgpool still open num_init_children.<br>
<br>
Because from Pgpool-II's point of view, your client (in your case<br>
tomcat's connection pooling) keeps on connecting to Pgpool-II. Until the<br>
client disconnects to Pgpool-II, it cannot close a connection to<br>
PostgreSQL.<br>
<br>
> Moreover, if I correctly understand connections with the same user name,<br>
> database, protocol version go to the same child process, so in my case, I'm<br>
> using only one of the child process and the others aren't used.<br>
<br>
No. See the FAQ entry which explains why:<br>
<a href="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" rel="noreferrer" target="_blank">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</a><br>
<br>
> Is there a way to use pgPool to load balancing sql queries from the same<br>
> user to a different child process?<br>
<br>
No.<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><br clear="all"><div><br></div>-- <br><div dir="ltr" class="gmail_signature">Enzo Veltri</div>