From: mark <dvlhntr@gmail.com>
To: 'Ivan Voras' <ivoras@freebsd.org>
To: pgsql-performance@postgresql.org
Subject: Re: Queries becoming slow under heavy load
Date: Wed, 26 Jan 2011 19:34:42 -0700
Message-ID: <01ed01cbbdca$c216aae0$464400a0$@com> (raw)
In-Reply-To: <ihp7bt$quc$1@dough.gmane.org>
References: <945629628BB0174D86709AFE6D1CDEF5016342E3@SP-EXCHMBC.sp.corp.collab.net>
<ihp7bt$quc$1@dough.gmane.org>
> -----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 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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: dvlhntr@gmail.com, ivoras@freebsd.org
Subject: Re: Queries becoming slow under heavy load
In-Reply-To: <01ed01cbbdca$c216aae0$464400a0$@com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox