Re: Recommend dba maintenance tasks on a regular bases

That output looks good with your settings.  It means you are using 501 of the
1000 max_fsm_relations and 57328 of the 153600 max_fsm_pages.

The check_postgres.pl nagios plugin that Greg Sabino Mullane maintains
recently added a check for FSM settings.  Check it out here:

http://bucardo.org/check_postgres/check_postgres.pl.html#fsm_pages

On Mon, 6 Oct 2008, Barbara Stephenson wrote:

> HI Jeff,
> Thank you for the link to explain FSM however I understand the concept where
> it would be faster to know where to store data based on an insert or an
> update but the results of the last few lines of the verbose I still don't get
> it.
>
> Our max_fsm_relations = 1000 and max_fsm_pages=153600
>
> We have auto vacuum running and below is the last few lines from a vacuum
> verbose statement.  Can you explain and do I need to adjust our settings?
>
> INFO:  free space map contains 51228 pages in 501 relations
> DETAIL:  A total of 57328 page slots are in use (including overhead).
> 57328 page slots are required to track all free space.
> Current limits are:  153600 page slots, 1000 relations, using 965 kB.
>
> Jeff Frost wrote:
>> On Fri, 12 Sep 2008, Barbara Stephenson wrote:
>>> 1- pg_dump - binary dump every midday and nightly
>>> 2 - auto vacuum
>>> 3- rotate data logs
>>
>> You should also consider running a script which does a VACUUM VERBOSE
>> weekly or twice monthly and emails you the last 8 lines of output.  This
>> will allow you to keep your FSM settings up to date.
>>
>> Jim Nasby's article here: http://decibel.org/~decibel/pervasive/fsm.html
>> has some good info about the Free Space Map if you're not familiar with it.
>>
>> In addition, it's probably worth setting log_min_duration_statement to
>> something like 500 or 1000 (500ms or 1s) so that you log slow queries.
>> Note that 500ms is just an example, set it to a value you consider slow so
>> that it will log your slow queries.  Then, after you've gathered some
>> data, run it through pgfouine.  http://pgfouine.projects.postgresql.org/
>
>

--
Jeff Frost, Owner     <jeff@frostconsultingllc.com>
Frost Consulting, LLC     http://www.frostconsultingllc.com/
Phone: 916-647-6411    FAX: 916-405-4032