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

Tatsuo Ishii ishii at sraoss.co.jp
Tue May 17 15:26:22 JST 2022 

> Hello Tatsuo,
> 
> This is just what I understood. I mean that when a client uses the
> Hikari Connection Pool connects to Pgpool, the number of child
> processes involved and hence the database connections grows up to the
> limit which is set by num_init_children.
> 
> Could you explain why is the number of involved child processes
> constantly growing in this case (Hikari Connection Pool connects to
> Pgpool), rather than already used ones being used?

As I already explained elsewhere, because pgpool child process do not
share connections to PostgreSQL.  For example even if pgpool child A
has the connection to database a and a is not used by clients, new
client cannot reuse the connection until it accidentaly conencts to
pgpool child A. I do not think this is a big problem because even if
there are many database connections grow up, they do not hurt the
perfomance of system as long as they are idle.

Also since Hikari keeps the connection to pgpool, pgpool cannot free
the database connections because from pgpool's point of view, the
client (actually Hikari) keeps on logging to the database.

> Thanks in advance.
> 
> Regards,
>    Nikola
> 
> On 13.05.2022 14:36, Tatsuo Ishii wrote:
>>> 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

More information about the pgpool-general mailing list