To: "Tom Lane" <tgl@sss.pgh.pa.us>,
 "Christopher Petrilli" <petrilli@gmail.com>
Cc: "Vivek Khera" <vivek@khera.org>, pgsql-performance@postgresql.org
Subject: Re: Impact of checkpoint_segments under continual load conditions 
References: <59d991c4050717100874c08a86@mail.gmail.com>
	<A50B8A1F-71A1-4F63-A5F4-1AA58F89557D@khera.org>
	<59d991c405071811457ccdd3f1@mail.gmail.com>
	<16394.1121715156@sss.pgh.pa.us>
	<59d991c405071812342e290f57@mail.gmail.com>
	<16794.1121718743@sss.pgh.pa.us>
	<59d991c405071907482bb0689b@mail.gmail.com>
	<op.st52tvunth1vuj@localhost>
	<59d991c4050719084460b376f1@mail.gmail.com>
	<4541.1121788647@sss.pgh.pa.us>
Message-ID: <op.st549abtth1vuj@localhost>
Date: Tue, 19 Jul 2005 18:25:48 +0200
From: PFC <lists@boutiquenumerique.com>
Organization: =?iso-8859-15?Q?La_Boutique_Num=E9rique?=
Content-Type: text/plain; format=flowed; delsp=yes; charset=iso-8859-15
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
In-Reply-To: <4541.1121788647@sss.pgh.pa.us>
User-Agent: Opera M2/8.0 (Linux, build 1095)


> I think PFC's question was not directed towards modeling your
> application, but about helping us understand what is going wrong
> (so we can fix  it).

	Exactly, I was wondering if this delay would allow things to get flushed,  
for instance, which would give information about the problem (if giving it  
a few minutes of rest resumed normal operation, it would mean that some  
buffer somewhere is getting filled faster than it can be flushed).

	So, go ahead with a few minutes even if it's unrealistic, that is not the  
point, you have to tweak it in various possible manners to understand the  
causes.

	And instead of a pause, why not just set the duration of your test to  
6000 iterations and run it two times without dropping the test table ?

	I'm going into wild guesses, but first you should want to know if the  
problem is because the table is big, or if it's something else. So you run  
the complete test, stopping a bit after it starts to make a mess, then  
instead of dumping the table and restarting the test anew, you leave it as  
it is, do something, then run a new test, but on this table which already  
has data.

	'something' could be one of those :
	disconnect, reconnect (well you'll have to do that if you run the test  
twice anyway)
	just wait
	restart postgres
	unmount and remount the volume with the logs/data on it
	reboot the machine
	analyze
	vacuum
	vacuum analyze
	cluster
	vacuum full
	reindex
	defrag your files on disk (stopping postgres and copying the database  
 from your disk to anotherone and back will do)
	or even dump'n'reload the whole database

	I think useful information can be extracted that way. If one of these  
fixes your problem it'l give hints.