Date: Tue, 22 Apr 2008 12:29:29 +0200
To: "Thomas Spreng" <spreng@socket.ch>,
	"Scott Marlowe" <scott.marlowe@gmail.com>
Subject: Re: Oddly slow queries
From: PFC <lists@peufeu.com>
Cc: "Chris Browne" <cbbrowne@acm.org>, pgsql-performance@postgresql.org
Content-Type: text/plain; format=flowed; delsp=yes; charset=utf-8
MIME-Version: 1.0
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>
	<dcc563d10804191004l1406fe89r5e2b7ee3931a4348@mail.gmail.com>
	<3D0211BE-8936-4705-964F-42E31263D88A@socket.ch>
Content-Transfer-Encoding: 7bit
Message-ID: <op.t90crftycigqcu@apollo13.peufeu.com>
In-Reply-To: <3D0211BE-8936-4705-964F-42E31263D88A@socket.ch>
User-Agent: Opera Mail/9.24 (Linux)


> 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
>