DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=CWJYJxlPFuk4Op/Sb2huDRFLcWSy3R/qSOEDpjXN8nd36we9UVcz7B3A2yUFfqAC8tkzkw88Kf9zBB/2uP+FeVMgKA9fqg+u+0e1NtlXLQ223IEI6kvk41umauiW2JZYr9p92tbvTXtwUcCkBlFPm16SuuD1GJiNACpCL8IGtYQ=
Message-ID: <59d991c40507190930244ba9bb@mail.gmail.com>
Date: Tue, 19 Jul 2005 12:30:34 -0400
From: Christopher Petrilli <petrilli@gmail.com>
Reply-To: Christopher Petrilli <petrilli@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: Impact of checkpoint_segments under continual load conditions
Cc: PFC <lists@boutiquenumerique.com>, Vivek Khera <vivek@khera.org>,
	pgsql-performance@postgresql.org
In-Reply-To: <4541.1121788647@sss.pgh.pa.us>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
References: <59d991c4050717100874c08a86@mail.gmail.com>
	<A50B8A1F-71A1-4F63-A5F4-1AA58F89557D@khera.org>
	<59d991c405071811457ccdd3f1@mail.gmail.com>
	<16394.1121715156@sss.pgh.pa.us>
	<59d991c405071812342e290f57@mail.gmail.com>
	<16794.1121718743@sss.pgh.pa.us>
	<59d991c405071907482bb0689b@mail.gmail.com>
	<op.st52tvunth1vuj@localhost>
	<59d991c4050719084460b376f1@mail.gmail.com>
	<4541.1121788647@sss.pgh.pa.us>

On 7/19/05, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Christopher Petrilli <petrilli@gmail.com> writes:=20
> > Not sure... my benchmark is designed to represent what the database
> > will do under "typical" circumstances, and unfortunately these are
> > typical for the application.  However, I can see about adding some
> > delays, though multiple minutes would be absurd in the application.
> > Perhaps a 5-10 second day? Would that still be interesting?
>=20
> I think PFC's question was not directed towards modeling your
> application, but about helping us understand what is going wrong
> (so we can fix it).  It seemed like a good idea to me.

OK, I can modify the code to do that, and I will post it on the web.

> The startup transient probably corresponds to the extra I/O needed to
> repopulate shared buffers with a useful subset of your indexes.  But
> just to be perfectly clear: you tried this, and after the startup
> transient it returned to the *original* trend line?  In particular,
> the performance goes into the tank after about 5000 total iterations,
> and not 5000 iterations after the postmaster restart?

This is correct, the TOTAL is what matters, not the specific instance
count.  I did an earlier run with larger batch sizes, and it hit at a
similar row count, so it's definately row-count/size related.
=20
> I'm suddenly wondering if the performance dropoff corresponds to the
> point where the indexes have grown large enough to not fit in shared
> buffers anymore.  If I understand correctly, the 5000-iterations mark
> corresponds to 2.5 million total rows in the table; with 5 indexes
> you'd have 12.5 million index entries or probably a couple hundred MB
> total.  If the insertion pattern is sufficiently random that the entire
> index ranges are "hot" then you might not have enough RAM.

This is entirely possible, currently:

shared_buffers =3D 1000          =20
work_mem =3D 65535               =20
maintenance_work_mem =3D 16384   =20
max_stack_depth =3D 2048         =20

> Again, experimenting with different values of shared_buffers seems like
> a very worthwhile thing to do.

I miss-understood shared_buffers then, as I thought work_mem was where
indexes were kept.  If this is where index manipulations happen, then
I can up it quite a bit.  The machine this is running on has 2GB of
RAM.

My concern isn't absolute performance, as this is not representative
hardware, but instead is the evenness of behavior.

Chris
--=20
| Christopher Petrilli
| petrilli@gmail.com