Re: Excessive growth of pg_attribute and other system tables

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Steve Crawford <scrawford(at)pinpointresearch(dot)com>
Cc: "Matthew T(dot) O'Connor" <matthew(at)zeut(dot)net>, pgsql-admin(at)postgresql(dot)org
Subject: Re: Excessive growth of pg_attribute and other system tables
Date: 2005-03-21 22:56:54
Message-ID: 2602.1111445814@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin pgsql-hackers

Steve Crawford <scrawford(at)pinpointresearch(dot)com> writes:
> Just to make sure I'm understanding things correctly this time...I
> originally (mis)understood these as settings related to resources
> used _during_ vacuuming. My current understanding is that they are
> basically pointers that track what space is available for reclamation
> by vaccum and that the amount of fsm resources required depends on
> both frequency of vacuums and volume of updates/deletes.

The FSM is where VACUUM stores pointers to the free space it's found
(or created) in each table. Subsequent INSERTs/UPDATEs will use this
free space instead of appending to the file. So to prevent table
growth, you need enough FSM slots to remember enough free space to
satisfy all the INSERTs/UPDATEs between successive VACUUM runs.

In practice people tend to allocate enough FSM to cover all of their
database, instead of worrying about exactly which pages might contain
free space. In a low-update-volume situation you could probably get
away with less.

> 2) What happens with all that free-space information at server restart
> (ie. does a server restart lead to dead-tuple leakage)?

Assuming you had a normal database shutdown rather than a crash, it's
written out at shutdown and reloaded. In any case, a VACUUM recomputes
the info from scratch.

> 4) Is there a way to query what proportion of the fsm resources are in
> use and would access to that info be useful to the autovacuum daemon
> or a system tuner?

VACUUM VERBOSE will tell you about this.

regards, tom lane

In response to

Browse pgsql-admin by date

  From Date Subject
Next Message Raghunath Ganti 2005-03-22 02:23:29 Contrib RPM Installation problem
Previous Message Jani Averbach 2005-03-21 21:49:40 pg_dump, custom format and changes in the dump file

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2005-03-21 23:15:51 Re: Proposal: OUT parameters for plpgsql
Previous Message Gavin Sherry 2005-03-21 22:32:07 Re: Proposal: OUT parameters for plpgsql