Re: strange fsm issues - Mailing list pgsql-admin

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