From: Scott Marlowe <scott.marlowe@gmail.com>
To: Thomas Spreng <spreng@socket.ch>
Cc: Chris Browne <cbbrowne@acm.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: Oddly slow queries
Date: Sat, 19 Apr 2008 11:04:06 -0600
Message-ID: <dcc563d10804191004l1406fe89r5e2b7ee3931a4348@mail.gmail.com> (raw)
In-Reply-To: <20B852C4-2ACA-445F-9289-558B6C86BAB4@socket.ch>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: scott.marlowe@gmail.com, spreng@socket.ch, cbbrowne@acm.org
Subject: Re: Oddly slow queries
In-Reply-To: <dcc563d10804191004l1406fe89r5e2b7ee3931a4348@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox