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(at)frostconsultingllc(dot)com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954