Received: from localhost (maia-2.hub.org [200.46.204.187])
	by postgresql.org (Postfix) with ESMTP id BB3EB9FB691
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Tue,  8 May 2007 07:13:58 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024)
	with ESMTP id 69967-04 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Tue,  8 May 2007 07:13:51 -0300 (ADT)
X-Greylist: whitelisted by SQLgrey-1.7.5
Received: from smtp8-g19.free.fr (smtp8-g19.free.fr [212.27.42.65])
	by postgresql.org (Postfix) with ESMTP id 57BD89FB5E6
	for <pgsql-performance@postgresql.org>;
	Tue,  8 May 2007 07:13:55 -0300 (ADT)
Received: from pulsar.staff.proxad.net (pulsar.staff.proxad.net
	[213.228.1.109])
	by smtp8-g19.free.fr (Postfix) with ESMTP id 3AECC198ED;
	Tue,  8 May 2007 12:13:54 +0200 (CEST)
Date: Tue, 8 May 2007 12:13:54 +0200 (CEST)
From: Pomarede Nicolas <npomarede@corp.free.fr>
X-X-Sender: npomarede@localhost
To: Guillaume Cottenceau <gc@mnc.ch>
cc: pgsql-performance@postgresql.org
Subject: Re: truncate a table instead of vaccum full when count(*)
 is 0
In-Reply-To: <877irjocow.fsf@meuh.mnc.lan>
Message-ID: <Pine.LNX.4.64.0705081210540.22289@localhost>
References: <Pine.LNX.4.64.0705081125140.20675@localhost>
	<877irjocow.fsf@meuh.mnc.lan>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200705/98
X-Sequence-Number: 24488

On Tue, 8 May 2007, Guillaume Cottenceau wrote:

> Pomarede Nicolas <npomarede 'at' corp.free.fr> writes:
>
>> Hello to all,
>>
>> I have a table that is used as a spool for various events. Some
>> processes write data into it, and another process reads the resulting
>> rows, do some work, and delete the rows that were just processed.
>>
>> As you can see, with hundreds of thousands events a day, this table
>> will need being vaccumed regularly to avoid taking too much space
>> (data and index).
>>
>> Note that processing rows is quite fast in fact, so at any time a
>> count(*) on this table rarely exceeds 10-20 rows.
>>
>>
>> For the indexes, a good way to bring them to a size corresponding to
>> the actual count(*) is to run 'reindex'.
>>
>> But for the data (dead rows), even running a vacuum analyze every day
>> is not enough, and doesn't truncate some empty pages at the end, so
>> the data size remains in the order of 200-300 MB, when only a few
>> effective rows are there.
>
> As far as I know, you probably need to increase your
> max_fsm_pages, because your pg is probably not able to properly
> track unused pages between subsequent VACUUM's.
>
> http://www.postgresql.org/docs/8.2/interactive/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-FSM
>
> Have you investigated this? It seems that you already know about
> the FSM stuff, according to your question about FSM and 8.3.
>
> You can also run VACUUM ANALYZE more frequently (after all, it
> doesn't lock the table).

thanks, but max FSM is already set to a large enough value (I'm running a 
vacuum analyze every day on the whole database, and set max fsm according 
to the last lines of vacuum, so all pages are stored in the FSM).


Nicolas