Re: strange fsm issues

On Wed, 21 Jun 2006, Jeff Frost wrote:

> The DB with the large objects that I had trouble dumping two weeks ago is now
> exhibiting some interesting fsm issues. The DB stores lots of large objects
> used for medical research statistics and the data is generally input during
> the day (9am-3pm pacific time) and evening (7pm-10pm pacific time). I
> noticed a fsm warning when vacuum verbose last week, so I had scheduled to
> increase max_fsm_pages to 50000. This was the warning I was receiving:
>
> Jun 20 09:22:58 newmars postgres[25754]: [2-2] HINT: Consider increasing the
> configuration parameter "max_fsm_pages" to a value over 42784.
>
> I increased the setting to 50000, restarted postgres and reran the vacuum
> verbose. I was greeted with the warning once again. :-(
>
> Jun 21 07:46:42 newmars postgres[4329]: [2-2] HINT: Consider increasing the
> configuration parameter "max_fsm_pages" to a value over 52128.
>
> Ok, I must not have increased it enough to accomodate the changes from
> yesterday to today...so, I increased it again to 60000 and re-ran the vacuum
> verbose:
>
> Jun 21 08:15:36 newmars postgres[4724]: [2-2] HINT: Consider increasing the
> configuration parameter "max_fsm_pages" to a value over 62608.
>
> What the heck? Nobody is accessing the DB but me....so I decided to just go
> overboard and set it to 100000. Changed it, restarted postgres, vacuum
> verbose:
>
> INFO: free space map contains 98441 pages in 125 relations
> DETAIL: A total of 100000 page slots are in use (including overhead).
> 102608 page slots are required to track all free space.
> Current limits are: 100000 page slots, 2000 relations, using 713 KB.
> NOTICE: number of page slots needed (102608) exceeds max_fsm_pages (100000)
> HINT: Consider increasing the configuration parameter "max_fsm_pages" to a
> value over 102608.
>
> Unfortunately my screen back buffer didn't have the other vacuum verbose
> outputs, so I had to pull the warnings out of the log file.
>
> Note that it's again exactly 2608 above the setting. That seems oddly
> coincidental. Any suggestions on this one? It's postgresql-8.1.4 compiled
> from the source tarball. Autovacuum is turned on and I'd love for it to be
> able to keep up.

So, I ran vacuumlo on the DB and it removed a few orphaned LOBs, but still
vacuum verbose yields the same.

Connected to vsl_cs
Checking datafile in public.study_action_history
Removed 15 large objects from vsl_cs.

INFO: free space map contains 98443 pages in 125 relations
DETAIL: A total of 100000 page slots are in use (including overhead).
102608 page slots are required to track all free space.
Current limits are: 100000 page slots, 2000 relations, using 713 KB.
NOTICE: number of page slots needed (102608) exceeds max_fsm_pages (100000)
HINT: Consider increasing the configuration parameter "max_fsm_pages" to a
value over 102608.

The DB is actually in active use now but the FSM suggestion is still 102608.
Very strange.

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