DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=H30q3EZGnOPwqZxoDxC7+MZilgCMAjw9mNhvfx30wciEqgza+f21jaxEGiyMWfMx6N8vvIK+F841B2fbvA7D4fr2fslkuOTl6xqQizgwCmXa8StmRHqTa7TS51Ksd4xuQoQ3XiwdScADJFId0ICjF4yyZKnFiLmhSzFwfLJbWzI=
Message-ID: <dcc563d10804191004l1406fe89r5e2b7ee3931a4348@mail.gmail.com>
Date: Sat, 19 Apr 2008 11:04:06 -0600
From: "Scott Marlowe" <scott.marlowe@gmail.com>
To: "Thomas Spreng" <spreng@socket.ch>
Subject: Re: Oddly slow queries
Cc: "Chris Browne" <cbbrowne@acm.org>, pgsql-performance@postgresql.org
In-Reply-To: <20B852C4-2ACA-445F-9289-558B6C86BAB4@socket.ch>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <3D7728ED-3D36-47E6-9266-351073ED4E11@socket.ch>
	<op.t9odybcgcigqcu@apollo13.peufeu.com>
	<AE4F81FF-C8BE-44BC-A79D-48EC5BF20162@socket.ch>
	<60y77du7qs.fsf@dba2.int.libertyrms.com>
	<20B852C4-2ACA-445F-9289-558B6C86BAB4@socket.ch>

On Wed, Apr 16, 2008 at 3:48 PM, Thomas Spreng <spreng@socket.ch> wrote:
>
>  On 16.04.2008, at 17:42, Chris Browne wrote:
>
> > spreng@socket.ch (Thomas Spreng) writes:
> >
> > > On 16.04.2008, at 01:24, PFC wrote:
> > >
> > > >
> > > >
> > > > > The queries in question (select's) occasionally take up to 5 mins
> > > > > even if they take ~2-3 sec under "normal" conditions, there are no
> > > > > sequencial scans done in those queries. There are not many users
> > > > > connected (around 3, maybe) to this database usually since it's
> > > > > still in a testing phase. I tried to hunt down the problem by
> > > > > playing around with resource usage cfg options but it didn't really
> > > > > made a difference.
> > > > >
> > > >
> > > >        Could that be caused by a CHECKPOINT ?
> > > >
> > >
> > > actually there are a few log (around 12 per day) entries concerning
> > > checkpoints:
> > >
> > > LOG:  checkpoints are occurring too frequently (10 seconds apart)
> > > HINT:  Consider increasing the configuration parameter
> > > "checkpoint_segments".
> > >
> > > But wouldn't that only affect write performance? The main problems I'm
> > > concerned about affect SELECT queries.
> > >
> >
> > No, that will certainly NOT just affect write performance; if the
> > postmaster is busy writing out checkpoints, that will block SELECT
> > queries that are accessing whatever is being checkpointed.
> >
>
>  What I meant is if there are no INSERT's or UPDATE's going on it shouldn't
>  affect SELECT queries, or am I wrong?

But checkpoints only occur every 10 seconds because of a high insert /
update rate.  So, there ARE inserts and updates going on, and a lot of
them, and they are blocking your selects when checkpoint hits.

While adjusting your background writer might be called for, and might
provide you with some relief, you REALLY need to find out what's
pushing so much data into your db at once that it's causing a
checkpoint storm.