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

[Tatsuo Ishii]

> 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