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

Nikola(HM) nikm71 at hotmail.com
Tue May 17 16:25:14 JST 2022 

Ok. I got it. Thanks a lot.

Regards,
    Nikola

On 17.05.2022 09:26, Tatsuo Ishii wrote:
>> 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