Message-ID: <67937db4-a681-4e28-adcc-da6b02e9964f@sjts.co.jp>
Date: Thu, 31 Jul 2025 22:08:22 +0900
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Sudden increase in connections in pgpool usage environment
To: Tatsuo Ishii <ishii@postgresql.org>
Cc: pgpool-general@lists.postgresql.org
References: <6c74ac0b-f943-4b27-ba7f-c6b3380faf4a@sjts.co.jp>
 <20250728.195522.1634717277013819274.ishii@postgresql.org>
 <ced5300d-8b5a-418c-8252-cfdcfa95add5@sjts.co.jp>
 <20250730.211324.1175750923831808216.ishii@postgresql.org>
Content-Language: en-US
From: =?UTF-8?B?6Yi05pyoIOa2vA==?= <r_suzuki@sjts.co.jp>
In-Reply-To: <20250730.211324.1175750923831808216.ishii@postgresql.org>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/67937db4-a681-4e28-adcc-da6b02e9964f%40sjts.co.jp>
Precedence: bulk

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


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

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.


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