From: Pomarede Nicolas <npomarede@corp.free.fr>
To: Guillaume Cottenceau <gc@mnc.ch>
Cc: pgsql-performance@postgresql.org
Subject: Re: truncate a table instead of vaccum full when count(*) is 0
Date: Tue, 8 May 2007 12:13:54 +0200 (CEST)
Message-ID: <Pine.LNX.4.64.0705081210540.22289@localhost> (raw)
In-Reply-To: <877irjocow.fsf@meuh.mnc.lan>
References: <Pine.LNX.4.64.0705081125140.20675@localhost>
<877irjocow.fsf@meuh.mnc.lan>
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-...
>
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: npomarede@corp.free.fr, gc@mnc.ch
Subject: Re: truncate a table instead of vaccum full when count(*) is 0
In-Reply-To: <Pine.LNX.4.64.0705081210540.22289@localhost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox