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

[Nikola(HM)]

Hello,

Thank you for the link on conversation chain 
https://www.pgpool.net/pipermail/pgpool-general/2021-October/007808.html

The same issue was discussed there. Because there it was about the fact 
that the pgpool client is a java application that uses Hikari CP for 
connection to database. While the Hikari CP itself is a connection pool.

And if pgpool doesn,t stop child processes forcibly according parameter 
child_idle_limit>0, the number of connections to database is growing and 
growing.

In last mail you mentioned about connection counter and you advised to 
use last version of Pgpool. I don't know what is connection counter, but 
I tried the last version of pgpool docker image 4.3.1 and found that 
with child_idle_limit=0 this behavior the same. Number of connections in 
database growing constantly. Pgpool doesn't give the same connection to 
client but allocates new child processes for client.

Or the fix of connection counter is not in pgpool docker image 4.3.1?

If not, please, tell how can I fix docker image?

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
>