From: Jan de Visser <jdevisser@digitalfairway.com>
To: pgsql-performance@postgresql.org
Subject: Re: Slow SELECTS after large update cycle
Date: Wed, 15 Mar 2006 19:42:00 -0500
Message-ID: <200603151942.01123.jdevisser@digitalfairway.com> (raw)
In-Reply-To: <1142464887.3859.195.camel@localhost.localdomain>
References: <200603151439.13958.jdevisser@digitalfairway.com>
<1142464887.3859.195.camel@localhost.localdomain>
On Wednesday 15 March 2006 18:21, Simon Riggs wrote:
> On Wed, 2006-03-15 at 14:39 -0500, Jan de Visser wrote:
> > After fixing the hanging problems I reported here earlier (by
> > uninstalling W2K3 SP1), I'm running into another weird one.
> >
> > After doing a +/- 8hr cycle of updates and inserts (what we call a
> > 'batch'), the first 'reporting' type query on tables involved in that
> > write cycle is very slow. As an example, I have a query which according
> > to EXPLAIN ANALYZE takes about 1.1s taking 46s. After this one hit,
> > everything is back to normal, and subsequent executions of the same query
> > are in fact subsecond. Restarting the appserver and pgsql does not make
> > the slowness re-appear, only running another batch will.
> >
> > During the 'write'/batch cycle, a large number of rows in various tables
> > are inserted and subsequently (repeatedly) updated. The reporting type
> > queries after that are basically searches on those tables.
> >
> > Anybody any ideas?
>
> This is caused by updating the commit status hint bits on each row
> touched by the SELECTs. This turns the first SELECT into a write
> operation.
>
> Try running a scan of the whole table to take the hit before you give it
> back to the users.
Thanks Simon. I didn't know about the cause, but I expected the answer to be
'deal with it', as it is. At least I can explain it now...
>
> Best Regards, Simon Riggs
jan
--
--------------------------------------------------------------
Jan de Visser jdevisser@digitalfairway.com
Baruk Khazad! Khazad ai-menu!
--------------------------------------------------------------
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: jdevisser@digitalfairway.com
Subject: Re: Slow SELECTS after large update cycle
In-Reply-To: <200603151942.01123.jdevisser@digitalfairway.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