"David Parker" <dparker@tazznetworks.com> writes:
> I was recently running a test with multiple client shell processes
> running psql commands (inserts) when all the client processes appeared
> to hang simultaneously. I assumed that I had an application deadlock
> somewhere, but after a few seconds - less than a minute, but certainly
> noticeable - all the clients picked up again and went on their way.
>
> In the database log at that time there was a "recycling transaction log"
> message which seems to correspond to the time when the clients were
> paused, though I don't have it concretely correlated.
I think what you saw was the disk being hogged by checkpoint writes.
"Recycling transaction log" is a routine operation, and by itself is a
reasonably cheap operation, but it's only done as the last step in a
checkpoint (in fact, from a technical point of view, it's done after the
checkpoint finishes). My guess is that the actual performance hit
occurred while the checkpoint was pushing out dirty buffers.
What you want is to reduce the amount of deferred I/O that has to happen
when a checkpoint occurs. There is not any way to do that before PG
8.0 (the obvious idea of reducing the interval between checkpoints is
counterproductive, IMHO). In 8.0 you can fool around with the bgwriter
parameters with an eye to "dribbling out" writes of dirty pages between
checkpoints.
regards, tom lane