pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedSlow SELECTS after large update cycle
4+ messages / 3 participants
[nested] [flat]
* Slow SELECTS after large update cycle
@ 2006-03-15 19:39 Jan de Visser <jdevisser@digitalfairway.com>
2006-03-15 23:11 ` Re: Slow SELECTS after large update cycle Chris <dmagick@gmail.com>
2006-03-15 23:21 ` Re: Slow SELECTS after large update cycle Simon Riggs <simon@2ndquadrant.com>
0 siblings, 2 replies; 4+ messages in thread
From: Jan de Visser @ 2006-03-15 19:39 UTC (permalink / raw)
To: pgsql-performance
Hello,
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?
Thanks,
jan
--
--------------------------------------------------------------
Jan de Visser jdevisser@digitalfairway.com
Baruk Khazad! Khazad ai-menu!
--------------------------------------------------------------
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Slow SELECTS after large update cycle
2006-03-15 19:39 Slow SELECTS after large update cycle Jan de Visser <jdevisser@digitalfairway.com>
@ 2006-03-15 23:11 ` Chris <dmagick@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: Chris @ 2006-03-15 23:11 UTC (permalink / raw)
To: Jan de Visser <jdevisser@digitalfairway.com>; +Cc: pgsql-performance
Jan de Visser wrote:
> Hello,
>
> 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.
After a large batch you need to run 'analyze' over the tables involved
to get postgresql to update it's statistics so it can work out which
indexes etc it should use.
--
Postgresql & php tutorials
http://www.designmagick.com/
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Slow SELECTS after large update cycle
2006-03-15 19:39 Slow SELECTS after large update cycle Jan de Visser <jdevisser@digitalfairway.com>
@ 2006-03-15 23:21 ` Simon Riggs <simon@2ndquadrant.com>
2006-03-16 00:42 ` Re: Slow SELECTS after large update cycle Jan de Visser <jdevisser@digitalfairway.com>
1 sibling, 1 reply; 4+ messages in thread
From: Simon Riggs @ 2006-03-15 23:21 UTC (permalink / raw)
To: Jan de Visser <jdevisser@digitalfairway.com>; +Cc: pgsql-performance
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.
Best Regards, Simon Riggs
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Slow SELECTS after large update cycle
2006-03-15 19:39 Slow SELECTS after large update cycle Jan de Visser <jdevisser@digitalfairway.com>
2006-03-15 23:21 ` Re: Slow SELECTS after large update cycle Simon Riggs <simon@2ndquadrant.com>
@ 2006-03-16 00:42 ` Jan de Visser <jdevisser@digitalfairway.com>
0 siblings, 0 replies; 4+ messages in thread
From: Jan de Visser @ 2006-03-16 00:42 UTC (permalink / raw)
To: pgsql-performance
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!
--------------------------------------------------------------
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2006-03-16 00:42 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-03-15 19:39 Slow SELECTS after large update cycle Jan de Visser <jdevisser@digitalfairway.com>
2006-03-15 23:11 ` Chris <dmagick@gmail.com>
2006-03-15 23:21 ` Simon Riggs <simon@2ndquadrant.com>
2006-03-16 00:42 ` Jan de Visser <jdevisser@digitalfairway.com>
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