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
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 |