Re: VACUUM not doing its job?

On Sat, 2002-08-03 at 19:39, Kristian Eide wrote:
> > You should also search the archives for threads on free space maps. You
> > most likely need to increase yours. In particular, see:
> >    http://archives.postgresql.org/pgsql-general/2002-07/msg00972.php
> 
> Thanks, very helpful, although there does not seem to be much description of
> what the two free space map options in postgresql.conf actually do. Doing a
> VACUUM ANALYZE VERBOSE on my largest table gives me:
> 
> NOTICE:  Pages 43681: Changed 208, Empty 0; Tup 1921387: Vac 61669, Keep 0,
> UnUsed 1362341.
> 
> I will try to increase the 'max_fsm_pages' option from 10000 to 500000 and
> see if that helps.
> 
> 
> Regards,
> 
> Kristian
> 

Note the high size of your unused value, this is a sure sign that your
not vacuuming as often as you are filling up your free space map.
Remember that for every insert/update/deletion pg will create an unused
tuple in the db. While the f_s_m attempts to keep track of these, once
it runs out of space, those unused tuples have little chance of being
recovered.  I'm trying to piece together some documentation on this, but
for now I'd recommend increasing the frequency of your vacuuming, which
should have very little performance impact and keep overall database
size smaller than if you increase the f_s_m. Remember also that if you
have a smaller subset of tables that generate most of your "tuple
turnover" that you can run vacuum on these tables specifically rather
than your whole db. hth,

Robert Treat