Re: Some vacuum & tuning help

Jeff <threshar(at)torgo(dot)978(dot)org> writes:
> Here's the msot recent vacuum for the "active" table. It gets a few
> hundred updates/inserts a minute constantly throughout the day.

> INFO: Pages 27781: Changed 0, Empty 0; Tup 2451648: Vac 0, Keep 0, UnUsed
> 1003361.
> Total CPU 2.18s/0.61u sec elapsed 2.78 sec.

> I see unused is quite high. This morning I bumped max_fsm_pages to 500000.
> If I'm thinking right you want unused and max_fsm to be closish, right?

No, they're unrelated. UnUsed is the number of currently-unused tuple
pointers in page headers, whereas the FSM parameters are measured in
pages. 30000 FSM slots would be more than enough for this table.

The above numbers don't seem terribly unreasonable to me, although
probably UnUsed would be smaller if you'd been vacuuming more often.
If you see UnUsed continuing to increase then you definitely ought to
shorten the intervacuum time.

VACUUM FULL does not reclaim unused tuple pointers AFAIR, except where
it is able to release entire pages at the end of the relation. So if
you really wanted to get back down to nil UnUsed, you'd need to do a
dump and reload of the table (or near equivalent, such as CLUSTER).
Not sure it's worth the trouble.

regards, tom lane