Re: Sudden increase in connections in pgpool usage environment

Sorry for late reply.

>> What do you mean by "even if a connection is not explicitly needed"?
>> Can you elaborate more?
>
> I apologize if my previous explanation was unclear.
> What I would like to confirm is this:
> Even if the web service itself does not explicitly initiate any
> database connections,
> is there any situation where pgpool might attempt to connect to
> PostgreSQL on its own?

No.

>> For pgpool To create 300 connections to PostgreSQL, you don't need to
>> have 300 web connections exactly at the same timing.
>
> For example, suppose the maximum number of connections from Tomcat to
> pgpool is 300,
> and the num_init_children parameter in pgpool is set to 600 (with
> max_pool set to 1).
>
> In this case, even if pgpool already holds 300 connections to
> PostgreSQL,
> is it possible — if the circumstances are unfavorable —
> that the remaining 300 child processes could also end up establishing
> connections,
> resulting in a total of up to 600 connections to PostgreSQL?

Yes. Suppose pgpool already has 300 connections to PostgreSQL, then
new connection request from client arrvies. If all 300 pgpool child
process have connections from clients (thus not in "waiting for
connection request" state), then the kernel wakes up a pgpool child
process which is in "waiting for connection request" state and let it
handle the incoming connection. If the process does not have a
connection pool to PostgreSQL, then the child process will create a
new connection to PostgreSQL. This could continue until all 600
"waiting for connection request" state process are in use.

> Also, is it correct to understand that in order to mitigate this
> situation,
> it is advisable to use settings such as client_idle_limit to
> appropriately discard unused connections?
>
> I would greatly appreciate it if you could kindly provide your
> response.

If you want to decrease the number of connections from pgpool to
PostgreSQL, you could set lower number to num_init_children.

If you do not want to lower num_init_children, you should consider
to tweak following parameters:

connection_life_time
client_idle_limit
child_life_time
child_max_connections

BTW, you are using Tomcat. Is it possible that Tomcat keeps connection
to pgpool? I am asking because I am not familiar with Tomcat. If so,
pgpool may fail to manage connection pooling because it does not have
an oppotunity to release connections to PostgreSQL even if the
parameters above are set.

> On 2025/07/30 21:13, Tatsuo Ishii wrote:
>>>> Is it possible Server1 overloaded? I am asking because on server1 3
>>>> kinds of servers are running. I suspect pgpool process are not enough
>>>> CPU being assigned, and cannot dispatch incoming new connections.
>>>
>>> Under normal conditions, the server load is not particularly
>>> problematic.
>>> Rather, it seems that the load increases at the same time the "DISCARD
>>> ALL" occurs.
>>>
>>> Regarding the phrase "cannot dispatch incoming new connections,"
>>> I was wondering―does pgpool sometimes establish connections to
>>> PostgreSQL automatically,
>>> even if a connection is not explicitly needed?
>> What do you mean by "even if a connection is not explicitly needed"?
>> Can you elaborate more?
>>
>>> My current understanding is that a connection to PostgreSQL is created
>>> only when Tomcat makes a request for one,
>>> and that pgpool initiates the connection at that point.
>> Yes.
>>
>>> Since it’s a bit hard to imagine the web system suddenly using up as
>>> many as 300 connections all at once,
>>> I just wanted to check and make sure I’m not misunderstanding how
>>> this works.
>> For pgpool To create 300 connections to PostgreSQL, you don't need to
>> have 300 web connections exactly at the same timing.
>> When a client connects pgpool, one of pgpool "child" process is chosen
>> by kernel. If the process already have a suitable connections cache
>> (i.e. matches the requested username/database etc.), no new connection
>> to PostgreSQL is created. However the process does not have, a new
>> connection to PostgreSQL is created. Since kernel does not care
>> whether the particular pgpool process already has the requested
>> connection to PostgreSQL, it is possible that a pgpool process which
>> does not have the connection. So if you are unlucky, continuous a few
>> number of concurrent requests from clients could make all 300 pgpool
>> process connect to PostgreSQL.
>> To deal with the situation, you can tweak configurations in "- Life
>> time -" section of pgpool.conf. See the manual for more details.
>> Best regards,
>> --
>> Tatsuo Ishii
>> SRA OSS K.K.
>> English: http://www.sraoss.co.jp/index_en/
>> Japanese:http://www.sraoss.co.jp
>
>
>