Re: Queries becoming slow under heavy load

From: "mark" <dvlhntr(at)gmail(dot)com>
To: "'Ivan Voras'" <ivoras(at)freebsd(dot)org>, <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Queries becoming slow under heavy load
Date: 2011-01-27 02:34:42
Message-ID: 01ed01cbbdca$c216aae0$464400a0$@com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance


> -----Original Message-----
> From: pgsql-performance-owner(at)postgresql(dot)org [mailto:pgsql-performance-
> owner(at)postgresql(dot)org] On Behalf Of Ivan Voras
> Sent: Wednesday, January 26, 2011 6:25 AM
> To: pgsql-performance(at)postgresql(dot)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 idle connections 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. (still get large user cpu load spikes though when a few hundred idle connection processes are woken open because they all appear 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 lose prepared statements... I am still working on that part and getting off 8.3. yes our app tried to do its own quasi connection pooling. When we deployed the app on a few hundred boxes the error of this choice years ago when this app lived on much fewer machines is now pretty evident.)

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

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Scott Marlowe 2011-01-27 04:18:34 Re: FW: Queries becoming slow under heavy load
Previous Message Bruce Momjian 2011-01-27 01:40:50 Re: anti-join chosen even when slower than old plan