Re: strange fsm issues

On Thu, 22 Jun 2006, Jim Nasby wrote:

>> CPU 0.00s/0.00u sec elapsed 0.00 sec.
>> INFO: free space map contains 3339998 pages in 125 relations
>> DETAIL: A total of 3341600 page slots are in use (including overhead).
>> 3341600 page slots are required to track all free space.
>> Current limits are: 3366499 page slots, 2000 relations, using 19853 KB.
>>
>> So then the question is..why was vacuum suggesting such low settings
>> previously?
>
> In any case, for this database I doubt it matters, since there's free space
> on every page... afaik any update will first try and add the new tuple on the
> current page, so as long as you have enough in the FSM to handle new inserts
> it won't matter.
>
> Of course, the fact that you have that much free space on every page makes me
> think it's not getting vacuumed enough...

Interestingly, it was just restored from a dump a week ago to do the 8.1.4
upgrade and autovacuum has been turned on with rather aggressive settings the
entire time. Now that we've got a workable FSM pages number, we're going to
vacuum full the db this weekend to get it back on track then watch the vacuum
verbose output and see how it's going.

pg_stat_database shows extremely limited activity:

vsl_cs=# select * from pg_stat_database ;
datid | datname | numbackends | xact_commit | xact_rollback | blks_read |
blks_hit
-------+-----------+-------------+-------------+---------------+-----------+----------
10793 | postgres | 0 | 527 | 0 | 896 |
42774
1 | template1 | 0 | 525 | 0 | 869 |
41190
16404 | vsl_cs | 1 | 2305 | 0 | 3623144 |
3709983

only 2,305 commits since 7:30 a.m. this morning when the db was restarted.
That's not too much..I wonder if having large objects makes for special
vacuuming fun..or maybe autovacuum can't deal with large objects? I didn't
find anything like that in the docs.

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