From: Peter Childs <peterachilds@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Vacuumdb - Max_FSM_Pages Problem.
Date: Mon, 26 Feb 2007 15:53:17 +0000
Message-ID: <a2de01dd0702260753k217a8adatc8b072765dfccb8@mail.gmail.com> (raw)
In-Reply-To: <45E2F232.3050707@livedatagroup.com>
References: <45E2F232.3050707@livedatagroup.com>
On 26/02/07, Pallav Kalva <pkalva@livedatagroup.com> wrote:
> Hi,
>
> I am in the process of cleaning up one of our big table, this table
> has 187 million records and we need to delete around 100 million of them.
>
> I am deleting around 4-5 million of them daily in order to catchup
> with vacuum and also with the archive logs space. So far I have deleted
> around 15million in past few days.
>
> max_fsm_pages value is set to 1200000. Vacuumdb runs once daily,
> here is the output from last night's vacuum job
>
>
> =======================================================================================
> INFO: free space map: 999 relations, 798572 pages stored; 755424
> total pages needed
> DETAIL: Allocated FSM size: 1000 relations + 1200000 pages = 7096
> kB shared memory.
> VACUUM
>
> ========================================================================================
>
> From the output it says 755424 total pages needed , this number
> keeps growing daily even after vacuums are done daily. This was around
> 350K pages before the delete process started.
>
> I am afraid that this number will reach the max_fsm_pages limit
> soon and vacuums thereafter will never catch up .
>
> Can anyone please explain this behavior ? What should I do to catch
> up with vacuumdb daily ?
>
Vacuum adds to free pages to the fsm so that they can be reused. If
you don't fill up those free pages the fsm will fill up. Once the fsm
is full no more pages can be added to the fsm. If you start writing to
the free pages via inserts when vacuum next runs more free pages will
be added that did not fit previously in the free space map due to it
being full.
If you are really deleting that many records you may be better coping
those you want to a new table and dropping the old one. To actually
recover space you need to either run vacuum full or cluster.
This ought to be in the manual somewhere as this question gets asked
about once a week.
Peter.
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: peterachilds@gmail.com
Subject: Re: Vacuumdb - Max_FSM_Pages Problem.
In-Reply-To: <a2de01dd0702260753k217a8adatc8b072765dfccb8@mail.gmail.com>
* 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