[pgpool-general: 7619] Re: Pgpool - ERROR: Sorry, too many clients already

[Tatsuo Ishii]

> Hi,
> 
> I incremented num_init_children from 120 to 200 today for testing. max_pool
> = 1 and connection_cache = off.
> We have 2 pgpool servers but the one that is connected to VIP one will be
> active and the other is for failover.

Ok.

> We have 2 backend postgres DB servers with max_connections 250.
> So the 1 * 200 < 250 rule is satisfied.

Right.

> My concern is whether there is any way to wait in the queue if the
> connections are more than 200 instead of throwing this error.
> We don't have long running queries sothe connections will be available shortly.

You can set reserved_connections = 0 to not throw the error. In this
case if more than 200 clients try to connect to Pgpool-II, they will
be queued in the listen queue. This should work well as long as you
don't have long running queries.

> On Thu, Jul 1, 2021 at 4:45 PM Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
> 
>> Looking into the pgppol.conf, I noticed num_init_children is 200,
>> rather than 120. Also it seems you have 2 Pgpool servers: db1 and db2.
>> Does your application servers accesses both db1 and db2? If so, the
>> required max_connections for PostgreSQL would be at least 400 because
>> 2 Pgpool servers connect to PostgreSQL with up to num_init_children =
>> 200 each, thus up to 400 connections will be made.
>>
>> > Hi,
>> >
>> > Pgpool-II version: pgpool-II version 4.2.2 (chichiriboshi)
>> > pgpool.conf attached. Kindly check.
>> >
>> > Thanks!
>> >
>> >
>> > On Thu, Jul 1, 2021 at 1:46 PM Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
>> >
>> >> > Hi,
>> >> >
>> >> > After integrating pgpool with the app server, we tested by running
>> load
>> >> > tests with 25 concurrent users.
>> >> > The following error comes multiple times while test running:
>> >> >
>> >> >> ERROR:  Sorry, too many clients already
>> >> >> LOG:  backend [1]: WARNING: there is already a transaction in
>> progress
>> >> >
>> >> >
>> >> > Set the following in pgpool:
>> >> > connection_cache=off
>> >> > child_life_time = 5min
>> >> > num_init_children=120
>> >> > max_pool=1
>> >> >
>> >> > There are master-slave postgre DB servers with max_connections=250.
>> >> > There are 2 appservers with XADatasource max pool size 60, pointing to
>> >> the
>> >> > Pgpool server.
>> >> > The application connects to 4 databases but only one database is used
>> >> 90%.
>> >> >
>> >> > When we point directly to the master DB server, all works fine. But
>> when
>> >> > connected via the Pgpool server, these errors come intermittently.
>> >> >
>> >> > Is it because Pgpool connections are not getting released and are all
>> in
>> >> > active state?
>> >> > Is there any way the excess connection requests in the queue instead
>> >> > of throwing errors so that it will be processed once existing
>> connections
>> >> > are released?
>> >> > Anyone please help me identify the bottleneck.
>> >>
>> >> Can you share the exact Pgpool-II version and pgpool.conf?
>> >>
>> >> Best regards,
>> >> --
>> >> Tatsuo Ishii
>> >> SRA OSS, Inc. Japan
>> >> English: http://www.sraoss.co.jp/index_en.php
>> >> Japanese:http://www.sraoss.co.jp
>> >>
>> >
>> >
>> > --
>> > Life is like this: "Just when we get all the answers of life.... God
>> > changes the question paper....
>> >
>> > Valsaraj Viswanathan
>>
> 
> 
> -- 
> Life is like this: "Just when we get all the answers of life.... God
> changes the question paper....
> 
> Valsaraj Viswanathan