Skip site navigation (1) Skip section navigation (2)

Re: Oddly slow queries

From: PFC <lists(at)peufeu(dot)com>
To: "Thomas Spreng" <spreng(at)socket(dot)ch>, "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com>
Cc: "Chris Browne" <cbbrowne(at)acm(dot)org>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Oddly slow queries
Date: 2008-04-22 10:29:29
Message-ID: op.t90crftycigqcu@apollo13.peufeu.com (view raw or flat)
Thread:
Lists: pgsql-performance
> that's correct, there are nightly (at least at the moment) processes that
> insert around 2-3 mio rows and delete about the same amount. I can see  
> that
> those 'checkpoints are occurring too frequently' messages are only logged
> during that timeframe.

	Perhaps you should increase the quantity of xlog PG is allowed to write  
between each checkpoint (this is checkpoint_segments). Checkpointing every  
10 seconds is going to slow down your inserts also, because of the need to  
fsync()'ing all those pages, not to mention nuking your IO-bound SELECTs.  
Increase it till it checkpoints every 5 minutes or something.

> I assume that it's normal that so many INSERT's and DELETE's cause the

	Well, also, do you use batch-processing or plpgsql or issue a huge mass  
of individual INSERTs via some script ?
	If you use a script, make sure that each INSERT doesn't have its own  
transaction (I think you know that since with a few millions of rows it  
would take forever... unless you can do 10000 commits/s, in which case  
either you use 8.3 and have activated the "one fsync every N seconds"  
feature, or your battery backed up cache works, or your disk is lying)...
	If you use a script and the server is under heavy load you can :
	BEGIN
	Process N rows (use multi-values INSERT and DELETE WHERE .. IN (...)), or  
execute a prepared statement multiple times, or copy to temp table and  
process with SQL (usually much faster)
	COMMIT
	Sleep
	Wash, rinse, repeat

> background writer to choke a little bit. I guess I really need to adjust  
> the
> processes to INSERT and DELETE rows in a slower pace if I want to do  
> other
> queries during the same time.
>
> cheers,
>
> tom
>



In response to

pgsql-performance by date

Next:From: Matthew WakelingDate: 2008-04-22 10:34:23
Subject: Re: Group by more efficient than distinct?
Previous:From: Thomas SprengDate: 2008-04-22 09:48:01
Subject: Re: Oddly slow queries

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group