Re: max_fsm_pages Sanity Check - Mailing list pgsql-admin

On Thu, 2003-01-02 at 19:15, HT Levine wrote:
> see my answers below:
> "Robert Treat" <xzilla@users.sourceforge.net> wrote in message
> news:1041547656.32015.38.camel@camel...
<snip>
> > >
> >
> > Tables with no deletions or updates won't benefit from vacuuming so
> > there's no reason to vacuum them.  On a table like Batch_Load_awaiting,
> > you need to do a vacuum after the 100% are updated. If you plan to
> > insert more into this table just do an --analyze, if you don't plan to
> > insert, then --full is more appropriate.  Keep an eye on those small
> > tables if they have frequent turnover. Even a 1000 row table that gets
> > updated every 5 minutes will generate more than 250,000 dead tuples a
> > day if your not regularly vacuuming.
>
> batch_load_awaiting (affectionately known as BLA)  gets N inserts directly
> followed by N updates once a day.  The previous day's update/inserts are not
> touched (except in a blue moon)   so I assume this would mean a daily vacuum
> analyze on this guy?
>

Yep, preferably right after your done updating.  If done regularly you
shouldn't have to vacuum full this table at all (any new rows can use
the previous days dead updated tuples)

> users depends on how often users decide to update their account, and orders
> would be even smaller (customer service changing user's orders)
>
> Got it on the small tables,  I'll keep an eye for those too -- thanks.
>
> >
> > I am also skeptical about your max_fsm_relations setting. This should be
> > equal to at least the number of rows output by \d in psql plus 100. Any
> > less and I question if your vacuum is being as effective as it should
> > be.
>
> sorry to sound dumb here ... but \d in psql gives me just 510 rows?    I was
> going to crank it up to 1 million (originally it had been set at 64,000)
>

what your cranking up to 1 million is max_fsm_pages, what I'm talking
about is max_fsm_relations.  _pages determines the total number of pages
of free space the database is willing to track, _relations determines
the total number of "objects" the database is willing to pay attention
to.  The default is set to 100, which means if you have more than 100
tables/indicies in your database, vacuum might ignore some tables that
are being updated. Theres no rule on how it picks which tables it pays
attention to (my guess is that its a first come first served thing, but
thats just a guess) but given that there are around 90 system "objects"
it's not hard to imagine that some things get left behind. You probably
need this set to at least 610, though fwiw the default on this was
recently bumped up to 1000 for future versions.

Robert Treat