Re: Impact of checkpoint_segments under continual load conditions

Christopher Petrilli <petrilli(at)gmail(dot)com> writes:
> On 7/19/05, PFC <lists(at)boutiquenumerique(dot)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