Re: Rapidly decaying performance repopulating a large table

On Tue, 2008-04-22 at 18:46 -0400, David Wilson wrote:

> I certainly expect some slowdown, given that I have indices that I
> can't drop (as you indicate above). Having been watching it now for a
> bit, I believe that the checkpoint settings were the major cause of
> the problem, however. Changing those settings has dropped the copy
> times back down toward what I'd expect; I have also now dropped the FK
> constraint, but that has made no perceptible difference in time.
>
> My guess at this point is that I'm just running into index update
> times and checkpoint IO. The only thing that still seems strange is
> the highly variable nature of the COPY times- anywhere from <1.0
> seconds to >20 seconds, with an average probably around 8ish. I can
> live with that, but I'm still open to any other suggestions anyone
> has!

I think it would be good to see some graphs of this.

The drop in speed can be explained by growing index size. The
variability in performance can be explained by variations in the data
distribution of the indexed column, i.e. the I/O isn't actually random
in the statistical sense. The speed of the COPY probably depends mostly
on how many infrequently occurring values you have in each set of loaded
data. However, that thinking could mask other problems.

Try log_statement_stats=on to see if the I/O is increasing per call and
that the I/O is correlated to the performance.

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com