Re: Cause of moving-target FSM space-needed reports

On Thu, 21 Sep 2006, Tom Lane wrote:

> 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.

It was indeed pg_largeobject that caused all the grief.  I have since put 
these settings in pg_autovacuum:

vsl_cs=# select * from pg_autovacuum ; vacrelid | enabled | vac_base_thresh | vac_scale_factor | anl_base_thresh |
anl_scale_factor| vac_cost_delay | vac_cost_limit
 

----------+---------+-----------------+------------------+-----------------+------------------+----------------+----------------
   2613 | t       |             150 |              0.1 |              75 |             0.05 |             -1 |
  -1
 
(1 row)

And it seems much happier these days:

INFO:  free space map contains 299025 pages in 144 relations
DETAIL:  A total of 296160 page slots are in use (including overhead).
296160 page slots are required to track all free space.
Current limits are:  5366499 page slots, 2000 relations, using 31572 KB.

Before the more aggressive autovacuum settings, we would have the problem crop 
up again when a researcher would delete several large objects at once. 
Apparently, each large object is around 80MB in size, so I suspect it's not 
difficult to overwhelm autovacuum if you remove quite a few of these at one 
time.

-- 
Jeff Frost, Owner     <jeff@frostconsultingllc.com>
Frost Consulting, LLC     http://www.frostconsultingllc.com/
Phone: 650-780-7908    FAX: 650-649-1954