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

[Tatsuo Ishii]

> 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?
> 
> Maybe there are some recommendations how to set up Pgpool with Hikari
> pool?

I am not familiar with Hikari and I cannot say any recommendations.
If Pgpool and Hikari are both connection pooler, why you need both?

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

No. Because child_life_time parameter fires after a client disconnects
to the pgpool child process.

> 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