Skip site navigation (1) Skip section navigation (2)

Re: Recommend dba maintenance tasks on a regular bases

From: Jeff Frost <jeff(at)frostconsultingllc(dot)com>
To: Barbara Stephenson <barbara(at)turbocorp(dot)com>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: Recommend dba maintenance tasks on a regular bases
Date: 2008-10-06 19:22:09
Message-ID: Pine.LNX.4.64.0810061220390.1406@discord.home.frostconsultingllc.com (view raw or flat)
Thread:
Lists: pgsql-admin
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(at)frostconsultingllc(dot)com>
Frost Consulting, LLC 	http://www.frostconsultingllc.com/
Phone: 916-647-6411	FAX: 916-405-4032

In response to

pgsql-admin by date

Next:From: Julie WardenDate: 2008-10-06 19:28:44
Subject: Re: pg_dump and roles
Previous:From: Barbara StephensonDate: 2008-10-06 19:09:44
Subject: Re: Recommend dba maintenance tasks on a regular bases

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group