Re: Slow Inserts on large tables

"Peter Childs" <peterachilds@gmail.com> writes:
> 2008/10/3 Peter Eisentraut <peter_e@gmx.net>:
>> Then show us your checkpointing-related parameters.

> I've currently got them set to

> checkpoint_segments = 3
> checkpoint_timeout = 180s
> checkpoint_completion_target = 0.5

> after reading that doing more smaller checkpoints might make each
> checkpoint work quicker and hence less of a performance hit when they
> actually happen.

That concept is actually pretty obsolete in 8.3: with spread-out
checkpoints it basically shouldn't hurt to increase the checkpoint
interval, and could actually help because the bgwriter doesn't have
such a tight deadline to finish the checkpoint.  In any case you
*definitely* need to increase checkpoint_segments --- the value
you've got could be forcing a checkpoint every few seconds not
every few minutes.

What I would suggest is turning on log_checkpoints and then seeing
if there's any correlation between your slow insert commands and the
checkpoints.  I'm suspicious that the problem is somewhere else.
(For instance, have you got anything that might take a lock on the
table?  Maybe enabling log_lock_waits would be a good idea too.)

            regards, tom lane