[pgpool-general: 8153] Re: allocation of child processes is constantly growing

[Bo Peng]

Hello,

> But I see that our microservice (that is, the Pgpool client) establishes 
> more and more new connections with Pgpool child processes, which remain 
> open and the number of connections to the database grows.
> 
> The microservice is written in Java and has its own Hikari pool of 
> connections.
> 
> Could you please explain this behaviour? Could this be the reason?

We have fixed some bugs with the connection counter.
It was reported by a user who uses Hikari pool.
I'm not sure if it is the same issue or not, but I recommend you to try the latest version.

https://www.pgpool.net/pipermail/pgpool-general/2021-October/007808.html
https://git.postgresql.org/gitweb/?p=pgpool2.git;a=commit;h=6ed89298fb9a71e5554ee44d4abe0cfd0f2da0f0

> Maybe there are some recommendations how to set up Pgpool with Hikari pool?
> 
> I tried to forcibly terminate connections from the client to Pgpool by 
> setting the client_idle_limit=300 parameter. In this case, everything is 
> fine, the number of connections to the database does not grow. BUT 
> client_idle_limit causes an error in the log which is not good.
> 
> Can it be replaced with the child_life_time parameter, which writes a 
> simple message to the log?
> 
> Regards,
>    Nikola
> 
> On 11.05.2022 12:49, Tatsuo Ishii wrote:
> >> Ok, I got it, thanks.
> >>
> >> That is, until the client closes the session, that is, it does not
> >> disconnect itself, it will work through a connection with the same
> >> child process. Even if it starts the next SELECT one minute after the
> >> first connection. Correctly?
> > Yes.
> >
> >> There are a couple of questions for this.
> >>
> >> 1. Why does a client make a connection to another child process if it
> >> already has a connection to the Pgpool child process?
> > No, a client will never make a new connection if it is already
> > connecting to pgpool child process (there is an exception: query
> > cancel. If a client wants to cancel current query, it needs to create
> > a new connection to pgpool. For example, psql does this)
> >
> >> 2. Since the connection pool cache is not shared among pgpool process,
> >> and our microservice (i.e. Pgpool client) works with only one database
> >> and with the same user, can we set the max_pool=1 parameter?
> > Sure. No problem.
> >
> >> Regards,
> >>     Nikolay
> >>
> >> On 11.05.2022 12:26, Tatsuo Ishii wrote:
> >>>> It turns out that one connection of the client to the child process is
> >>>> only for one request to the database?
> >>> You mean "request" is a SQL command? If so, the answer is no. A client
> >>> can send as many SQL command as it wants before finishes the session
> >>> to the pgpool child process.
> >>>
> >>>> Although the client has already established the connection to the
> >>>> particular child process, could the client's next request be to that
> >>>> same child process, or maybe to a different one?
> >>>> Regards,
> >>> Thus the next request will be sent to the same child process as long
> >>> as the session continues.
> >>>
> >>>>     Nikola
> >>>>
> >>>> On 11.05.2022 12:01, Tatsuo Ishii wrote:
> >>>>> Because connection pool cache is not shared among pgpool process as
> >>>>> explained in the FAQ.
> >>>>>
> >>>>>> And this connection remains
> >>>>> But other child process never knows the same database connection is in
> >>>>> other child process. So there's no way for the new child process to
> >>>>> reuse the connection.
> >>>>>
> >>>>>> Thanks for the reply. But still. The situation is this.
> >>>>>>
> >>>>>> The microservice connects to the child process and through it to the
> >>>>>> database. And this connection remains. Then the same microservice with
> >>>>>> the same user to the same database creates a new connection with
> >>>>>> another child process rather than reusing the old one that already
> >>>>>> exists. Why?
> >>>>>>
> >>>>>> Regards,
> >>>>>>      Nikolay
> >>>>>>
> >>>>>> On 11.05.2022 09:29, Tatsuo Ishii wrote:
> >>>>>>> Hi,
> >>>>>>>
> >>>>>>>> Hello,
> >>>>>>>>
> >>>>>>>> We are running an application in Kubernetes. Pgpool also works in
> >>>>>>>> Kubernetes. Each microservice has its own PostgreSQL
> >>>>>>>> database. Microservices are written in Java and connect to their
> >>>>>>>> databases via Pgpool .
> >>>>>>>>
> >>>>>>>> We have a very hot issue with Pgpool.
> >>>>>>>>
> >>>>>>>> Even in quiet mode, when there is no activity, the number of busy
> >>>>>>>> child processes and, accordingly, connections to databases is
> >>>>>>>> constantly growing on PostgreSQL and on  and does not decrease,
> >>>>>>>> reaching the limit. It turns out that microservices make new
> >>>>>>>> connections to the database each time through a new child process, and
> >>>>>>>> do not reuse the existing one.
> >>>>>>>>
> >>>>>>>> Could you please tell me why this is happening.
> >>>>>>>>
> >>>>>>>> Explain, please, the mechanism of allocation of child process in
> >>>>>>>> Pgpool for client connections.
> >>>>>>>>
> >>>>>>>> When an existing client connection to a child process is reused and
> >>>>>>>> when not?
> >>>>>>> Please see the FAQ:
> >>>>>>> https://pgpool.net/mediawiki/index.php/FAQ#Is_connection_pool_cache_shared_among_pgpool_process.3F
> >>>>>>>
> >>>>>>> Best reagards,
> >>>>>>> --
> >>>>>>> Tatsuo Ishii
> >>>>>>> SRA OSS, Inc. Japan
> >>>>>>> English: http://www.sraoss.co.jp/index_en.php
> >>>>>>> Japanese:http://www.sraoss.co.jp
> _______________________________________________
> pgpool-general mailing list
> pgpool-general at pgpool.net
> http://www.pgpool.net/mailman/listinfo/pgpool-general


-- 
Bo Peng <pengbo at sraoss.co.jp>
SRA OSS, Inc. Japan
http://www.sraoss.co.jp/