From: Tom Lane <tgl@sss.pgh.pa.us>
To: Steve Crawford <scrawford@pinpointresearch.com>
Cc: Matthew T. O'Connor <matthew@zeut.net>
Cc: pgsql-admin@postgresql.org
Subject: Re: Excessive growth of pg_attribute and other system tables
Date: Mon, 21 Mar 2005 17:56:54 -0500
Message-ID: <2602.1111445814@sss.pgh.pa.us> (raw)
In-Reply-To: <200503211235.22731.scrawford@pinpointresearch.com>
References: <200503171515.38214.scrawford@pinpointresearch.com>
<423F17D7.7040904@zeut.net>
<1219.1111434030@sss.pgh.pa.us>
<200503211235.22731.scrawford@pinpointresearch.com>
Steve Crawford <scrawford@pinpointresearch.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
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, scrawford@pinpointresearch.com, matthew@zeut.net
Subject: Re: Excessive growth of pg_attribute and other system tables
In-Reply-To: <2602.1111445814@sss.pgh.pa.us>
References: <200503171515.38214.scrawford@pinpointresearch.com> <423F17D7.7040904@zeut.net> <1219.1111434030@sss.pgh.pa.us> <200503211235.22731.scrawford@pinpointresearch.com> <2602.1111445814@sss.pgh.pa.us>
* 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