We've seen reports occasionally of how the system reports that
max_fsm_pages needs to be increased to at least X, and then when the DBA
does so, it complains that some larger amount is needed --- one recent
report is here:
http://archives.postgresql.org/pgsql-admin/2006-06/msg00176.php
I finally got around to looking at this, and I see what is going on.
The reported number is based on the aggregate of "request" page counts
passed by VACUUM to the FSM module. However, vacuumlazy.c is
prefiltering its requests to at most MaxFSMPages, because it knows that
no more than that many pages will be accepted anyway. So if you have a
single table containing more than max_fsm_pages pages with interesting
amounts of free space, the reported total is being artificially
constrained, and then when you relax the constraint, the reported total
jumps up. In the case Jeff describes where it kept saying max_fsm_pages
plus 2608 were needed, I suppose he had boatloads of free space in one
table (probably pg_largeobject) and exactly 2608 interesting pages in
all other tables.
I think it's reasonable for vacuumlazy.c to track at most MaxFSMPages
pages as it's doing now --- but it should keep a separate count of the
total number of pages with at least threshold amount of free space, and
pass that as a separate argument to RecordRelationFreeSpace. This will
not take any more space in shared memory than we already use, but it
will allow us to report a truthful value for "number of pages needed",
which we clearly are failing to do now.
It might also be a good idea if vacuum verbose reported this page count,
since when you've got a single table bloated like this, VACUUM FULL or
CLUSTER might be a more appropriate solution than increasing the FSM
size --- but there's no way to know which rel is the problem from the
FSM total. In fact, maybe vacuum should just throw a WARNING when it
finds a single rel with more than MaxFSMPages pages with useful free space?
Comments? I'd like to put in a fix for beta1, which means today ...
regards, tom lane