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

[Tatsuo Ishii]

It depends on the CPU power. I suggest you to test serialize_accept =
on/off settings. The doc explains how to perform the test.

> Is it recommended to use serialize_accept = on when num_init_children 200
> or more?
> 
> On Thu, Jul 1, 2021 at 7:51 PM Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
> 
>> > 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
>>
> 
> 
> -- 
> Life is like this: "Just when we get all the answers of life.... God
> changes the question paper....
> 
> Valsaraj Viswanathan