Received: from localhost (unknown [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id A306E2E0106
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Tue, 15 Jan 2008 11:51:13 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024)
	with ESMTP id 40715-03 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Tue, 15 Jan 2008 11:51:01 -0400 (AST)
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
	by postgresql.org (Postfix) with ESMTP id 482E82E00CD
	for <pgsql-performance@postgresql.org>;
	Tue, 15 Jan 2008 11:51:10 -0400 (AST)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.14.1/8.14.1) with ESMTP id m0FFp4jw024098;
	Tue, 15 Jan 2008 10:51:05 -0500 (EST)
To: Adrian Moisey <adrian@careerjunction.co.za>
cc: pgsql-performance@postgresql.org
Subject: Re: Linux/PostgreSQL scalability issue - problem with 8 cores 
In-reply-to: <478CA662.4060505@careerjunction.co.za> 
References: <flii57$748$1@news.hub.org> <7083.1199379570@sss.pgh.pa.us>
	<477D1D59.7050002@comgate.cz> <4782063C.7070105@comgate.cz>
	<26502.1199745090@sss.pgh.pa.us> <4782AB4D.8010009@comgate.cz>
	<27655.1199750492@sss.pgh.pa.us> <47836A4C.5080707@comgate.cz>
	<10713.1199813111@sss.pgh.pa.us> <47874EE8.2050406@comgate.cz>
	<12997.1200063940@sss.pgh.pa.us> <478B4BB9.4030208@comgate.cz>
	<13894.1200324134@sss.pgh.pa.us> <478CA26E.9040105@comgate.cz>
	<478CA662.4060505@careerjunction.co.za>
Comments: In-reply-to Adrian Moisey <adrian@careerjunction.co.za>
	message dated "Tue, 15 Jan 2008 14:26:10 +0200"
Date: Tue, 15 Jan 2008 10:51:04 -0500
Message-ID: <24097.1200412264@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200801/127
X-Sequence-Number: 28366

Adrian Moisey <adrian@careerjunction.co.za> writes:
>> we've found it: TRUNCATE

> I haven't been following this thread.  Can someone please explain to me 
> why TRUNCATE causes these spikes?

It's not so much the TRUNCATE as the overhead of broadcasting the
resultant catalog changes to the many hundreds of (mostly idle)
backends he's got --- all of which respond by trying to lock the
shared sinval message queue at about the same time.

You could see the whole thread as an object lesson in why connection
pooling is a good idea.  But certainly it seems that sinval is the
next bottleneck in terms of being able to scale Postgres up to very
large numbers of backends.

			regards, tom lane