DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type;
	b=mFJ4quvn4Pg0CaNU+vC9PJQ7YtAW6AEd5CSAMT/HyO5twn7wefDqtwCgEoNBtK3nG6
	+BO//Uk58vC8QXcU19ALsXWnHwL0avqF2J8p4vIKqhvUnmGozqatKb0yjcX+hVw4h38d
	K66oOkoB5ybpu20WTLzbb2qF2VxFm2YEqZ/qs=
MIME-Version: 1.0
In-Reply-To: <1303464095907-4332601.post@n5.nabble.com>
References: <1303464095907-4332601.post@n5.nabble.com>
Date: Wed, 11 May 2011 23:36:19 -0400
Message-ID: <BANLkTikFUqMuC6Xrt7kd91FKTzXCCAT7MQ@mail.gmail.com>
Subject: Re: Checkpoint execution overrun impact?
From: Robert Haas <robertmhaas@gmail.com>
To: drvillo <f.vivoli@gmail.com>
Cc: pgsql-performance@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1

On Fri, Apr 22, 2011 at 5:21 AM, drvillo <f.vivoli@gmail.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