Re: Unexpected disk space growth controlling measures

On Sat, Jul 31, 2010 at 1:00 AM, Gnanakumar <gnanam(at)zoniac(dot)com> wrote:
> Hi Scott,
>
> Thanks for your valuable suggestion.  Our production server is running
> PostgreSQL 8.2.3 on Red Hat 4.1.1-52.

Definitely look at updating to the latest 8.2 release, there's a lot
of bug fixes since 8.2.3.

>
>>  Esp in 8.3 and before where blowing out the free space map is quite easy
> to do and it can go unnoticed for some time.
>
> As you pointed out rightly, recently in our Production server there is a
> warning showing up in VACUUM ANALYZE VERBOSE.
> WARNING:  relation "public.oneofmytable" contains more than "max_fsm_pages"
> pages with useful free space
> HINT:  Consider compacting this relation or increasing the configuration
> parameter "max_fsm_pages".
>
> Currently, there are 439 tables and 743 indexes, adding up 1182 relations.
> What would you recommend me to set the value for "max_fsm_pages" and
> "max_fsm_relations" parameters?

Usually I set max_fsm_pages to 2x or more whatever vacuum verbose says
it needs. As for max_fsm_relations, it only needs to be big enough
to hold all tables and indexes, so if you've got 1182, then 2000 or so
would be fine. I work with one db that has 50k or more tables and
indexes, and on that one we have it set to something lik 500k so we
don't hit the limit.

>> Where I work we use about 2.5M entries but have our fsm set to 10M so we
> don't have to worry about blowing it out overnight or anything.
> If you don't mind, can you make me clear here.  Is 2.5M entries in a single
> table or is it something else?

max_fsm_pages is set to 10M

>> Lastly, make sure your IO subsystem can keep up.  If you're on the hairy
> edge, then vacuum may never be able to keep up.
> How do I confirm/make sure that IO subsystem can keep up?

Keep an eye on your system with tools like iostat.

iostat -xd 10 /dev/sdb

for instance if your db is on /dev/sdb. Keep an eye on %Util. If
it's always at 100% for hours on end, then your IO subsystem is likely
maxed out.

--
To understand recursion, one must first understand recursion.