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

[Enzo Veltri]

Just as follow-up:
all the idle connections are due to our strategy of id generation (very
good explanation of the problem here -->
https://vladmihalcea.com/why-you-should-never-use-the-table-identifier-generator-with-jpa-and-hibernate/
).
We changed the strategy using postgres sequences and all the idle
connection were gone.
So it wasn't a problem of pgpool.

Best regards

Il giorno sab 4 mag 2019 alle ore 23:44 Tatsuo Ishii <ishii at sraoss.co.jp>
ha scritto:

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


-- 
Enzo Veltri
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20190507/da6bfed6/attachment.html>