OK, it's quite some time from when the original question was posted, but
now I have more data... see below.
On Thu, 2005-09-29 at 19:24, Jim C. Nasby wrote:
> On Tue, Sep 27, 2005 at 10:24:02AM +0200, Csaba Nagy wrote:
> > Hi all,
> >
> > I've recently asked a similar question, which received no useful answer
> > yet, so I'll drop in too.
> >
> > In my case, the table I was inserting to was a quite big one already to
> > start with (and analyzed so), so I was expecting that it will not slow
> > down due to indexes, as they were quite big to start with as I said.
> >
> > What I mean is that I expected that the speed will be more or less
> > constant over the whole inserting. But the result was that after a while
> > the average insert speed dropped considerably and suddenly, which I
> > can't explain and would like to know what caused it...
> > The table was ~100 million live rows and quite often updated, and the
> > insert was ~40 million rows. After ~10 million rows the average speed
> > dropped suddenly about 4 times.
> >
> > My only suspicion would be that the table had a quite big amount of free
> > space in it at the beginning due to the fact that it is quite often
> > updated, and then the free space was exhausted. So the speed difference
> > might come from the difference in using free space versus creating new
> > pages ? Or the same thing for the b-tree indexes.
> >
> > Is there any other reasonable explanation for this ? As I see this kind
> > of behavior consistently, speed OK on start of inserting, and then slow
> > down, and I would like to know if I can tune my DB to cope with it or
> > just accept that it works like this...
> >
> > Cheers,
> > Csaba.
>
> I can't think of any explanation for this off-hand. Can you re-run the
> test on a table that doesn't have a bunch of free space in it to see if
> that's what the issue was?
So the issue was that the system had other scheduled heavy activities
running I was not aware of. So when they started, the insert performance
dropped... so I guess it is all clear now, at least for me... it's the
typical case of the right hand doesn't know what the left hand does, and
the head spends a lot of time figuring out what both were doing ;-)
Cheers,
Csaba.