DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=from:to:references:in-reply-to:subject:date:message-id:mime-version
	:content-type:content-transfer-encoding:x-mailer:thread-index
	:content-language;
	b=whZBeHOkXoMxDr6Yr14cDWukMCIpjgYQNvjZaBt6tkiPhcNMcj+9cfoFCy3D/+xRZd
	4ECQXjBXD519LNYn2QDe9ZbQCJqQvWj8X7Ju6xzh7vTyeYxJFiHwTKb3YgybQp2nEPKT
	Uc2JOFXVcBYI5ElgR0ZQXovbhD99BynKC+LZQ=
From: "mark" <dvlhntr@gmail.com>
To: "'Ivan Voras'" <ivoras@freebsd.org>,
	<pgsql-performance@postgresql.org>
References: 
 <945629628BB0174D86709AFE6D1CDEF5016342E3@SP-EXCHMBC.sp.corp.collab.net>
	<ihp7bt$quc$1@dough.gmane.org>
In-Reply-To: <ihp7bt$quc$1@dough.gmane.org>
Subject: Re: Queries becoming slow under heavy load
Date: Wed, 26 Jan 2011 19:34:42 -0700
Message-ID: <01ed01cbbdca$c216aae0$464400a0$@com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Thread-Index: Acu9XJQgvd79hrWYTk+Um/FixGKjwQAbOaYA
Content-Language: en-us


> -----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
>=20
> 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.
>=20
> 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.
>=20
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.=20


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

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