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

[valsaraj pv]

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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20210702/f78baa8d/attachment.htm>