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