Re: Slow INSERT

Michal Taborsky <michal(at)taborsky(dot)cz> writes:
> I ran some tests to support this hypothesis. Every 500th insert is a tad
> slower, but it is insignificant (normally the INSERT lasts 1.5ms, every
> 500th is 9ms). During my tests (10 runs of 1000 INSERTS) I had
> experienced only one "slow" insert (2000ms). It is clearly caused by
> other processes running on this server, but such degradation of
> performance is highly suspicious, because the server very rarely goes
> over load 1.0.

Actually, the simpler theory is that the slowdown is caused by
background checkpoint operations. Now a checkpoint would slow
*everything* down not only this one insert, so maybe that's not
the right answer either, but it's my next idea. You could check
this to some extent by manually issuing a CHECKPOINT command and
seeing if you get an insert hiccup. Note though that closely
spaced checkpoints will have less effect, because less I/O will
be triggered when not much has changed since the last one. So
you'd want to wait a bit between experiments.

regards, tom lane