Re: Queries becoming slow under heavy load

> -----Original Message-----
> From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-
> owner@postgresql.org] On Behalf Of Ivan Voras
> Sent: Wednesday, January 26, 2011 6:25 AM
> To: pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] Queries becoming slow under heavy load
>
> On 25/01/2011 22:37, Anne Rosset wrote:
> > Hi,
> >
> > We are running some performances tests.  With a lot of concurrent
> > access,  queries get very slow. When there is no load, those queries
> run
> > fast.
>
> As others said, you need to stat how many concurrent clients are
> working
> on the database and also the number of logical CPUs (CPU cores,
> hyperthreading) are present in the machine. So far, as a rule of thumb,
> if you have more concurrent active connections (i.e. doing queries, not
> idling), you will experience a sharp decline in performance if this
> number exceeds the number of logical CPUs in the machine.
>
Depending on what version the OP is running - I didn't see where a version was givin - if there is a "lot" number of
idleconnections it can affect things as well. Tom indicated to me this should be "much better" in 8.4 and later.  


<slight deviation to put idle connections overhead in prespecive>
We cut our idle connections from 600+ to a bit over 300 and saw a good drop in box load and query responsiveness.
(stillget large user cpu load spikes though when a few hundred idle connection processes are woken open because they
allappear to be sleeping on the same semaphore and one of them has work to do. ) 

(yeah I know get a pooler, to bad only bouncer seems to pool out idle connections with transaction pooling but then I
loseprepared statements... I am still working on that part and getting off 8.3. yes our app tried to do its own quasi
connectionpooling. When we deployed the app on a few hundred boxes the error of this choice years ago when this app
livedon much fewer machines is now pretty evident.) 

>
> --
> Sent via pgsql-performance mailing list (pgsql-
> performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance