Re: Checkpoint execution overrun impact?

On Fri, Apr 22, 2011 at 5:21 AM, drvillo <f(dot)vivoli(at)gmail(dot)com> wrote:
> -given the configuration attached (which is basically a vanilla one) and the
> number of buffers written at each execution, are these execution times
> normal or above average?

They seem fine. Remember that the write is deliberately spread out;
it's not as if the system couldn't write out 130-160 8k blocks in less
than 30 s.

> -in the case of the execution that overruns past the timeout, what are the
> implications wrt the client application?

Not sure what you are referring to here.

> -AFAIU client connections are basically stalled during checkpoints. Is it
> reasonable to infer that the fact that the application blocking on a
> getConnection() might be related to checkpoints being executed?
> -considering some tuning on the PG side, should I try increasing
> checkpoint_timeout and rising checkpoint_completion_target to lessen the
> impact of IO on the client or should I shorten the period so there's less
> stuff to write? from the number of buffers written on average I'd assume the
> first option is the one to go for but I might miss some bit of reasoning
> here...

I'm a bit puzzled by all of this because the logs you posted seem to
reflect a system under very light load. Each checkpoint is writing no
more than 4% of shared_buffers and the sync phases are generally
completing in less than one second. I don't see why that would be
causing stalls.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company