From: Tom Lane <tgl@sss.pgh.pa.us>
To: Christopher Petrilli <petrilli@gmail.com>
Cc: PFC <lists@boutiquenumerique.com>
Cc: Vivek Khera <vivek@khera.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: Impact of checkpoint_segments under continual load conditions
Date: Tue, 19 Jul 2005 11:57:27 -0400
Message-ID: <4541.1121788647@sss.pgh.pa.us> (raw)
In-Reply-To: <59d991c4050719084460b376f1@mail.gmail.com>
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>
Christopher Petrilli <petrilli@gmail.com> writes:
> On 7/19/05, PFC <lists@boutiquenumerique.com> wrote:
>> What happens if, say at iteration 6000 (a bit after the mess starts), you
>> pause it for a few minutes and resume. Will it restart with a plateau like
>> at the beginning of the test ? or not ?
> 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?
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.
> Well, I don't have the numbers any more, but restarting the postmaster
> has no effect, other than the first few hundreds COPYs are worse than
> anything (3-4x slower), but then it goes back to following the trend
> line. The data in the chart for v8.0.3 includes running pg_autovacuum
> (5 minutes).
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?
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.
Again, experimenting with different values of shared_buffers seems like
a very worthwhile thing to do.
regards, tom lane
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: tgl@sss.pgh.pa.us, petrilli@gmail.com, lists@boutiquenumerique.com, vivek@khera.org
Subject: Re: Impact of checkpoint_segments under continual load conditions
In-Reply-To: <4541.1121788647@sss.pgh.pa.us>
* 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