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

[valsaraj pv]

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.
We have 2 backend postgres DB servers with max_connections 250.
So the 1 * 200 < 250 rule is satisfied.

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.


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