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

[Nikola(HM)]

Hello,

It looks like this is what we need. I'll see.

Thank you very much.

Tell me, what version do you suggest upgrade to? We have 4.2.2.

Regards,
   Nikola

On 12.05.2022 07:41, Bo Peng wrote:
> 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
>