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

[Tatsuo Ishii]

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

Probably you misunderstand the meaning of pgpool's configuration
parameters. The number connections to PostgreSQL backend is limited by
num_init_children * max_pool. It's not growing unlimited (except query
cancel). If you see that the the number of connections to PostgreSQL
backend is growing more than num_init_children * max_pool, please let
us know. (I never had such a report from users, though).

Setting appropriate values to num_init_children and max_pool is totally
user's responsibility, not pgpool.

> 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
>>
> _______________________________________________
> pgpool-general mailing list
> pgpool-general at pgpool.net
> http://www.pgpool.net/mailman/listinfo/pgpool-general