Re: disk space usage enlarging despite vacuuming - Mailing list pgsql-general

I assume your talking about the MAX_FSM_RELATIONS setting in
postgresql.conf?

What are the drawbacks to setting this too high? My database has about
1million (very small row) inserts, and 1 million deletes each day, with
1 table exceeding 5.5million rows, and another just under 1million.

Currently MAX_FSM_RELATIONS is set to 10,000.

select count(*) from pg_class where not relkind in ('i','v');
 count
-------
   144
(1 row)

select sum(relpages) from pg_class where relkind in ('r','t');
  sum
-------
 77918
(1 row)

I remember reading MAX_FSM_RELATIONS should be higher then the first
query, and lower then the last query, but thats a huge difference. What
would be the advantages/disadvantages to setting MAX_FSM_RELATIONS to
75,000?

Where does MAX_FSM_PAGES fall in to this?


On Mon, 2003-05-19 at 16:35, Tom Lane wrote:
> Ron Snyder <snyder@roguewave.com> writes:
> >>>> What's your turnover rate for updating or deleting large objects?
> >>> There's probably only about 10K additions/day, and there
> >>> should be about 7500 deletions/day.
> >>
> >> How large are the objects in question?
>
> > They average 24K (or less).
>
> So an average update or delete touches at least three pages of
> pg_largeobject, probably more.  It'd probably be reasonable to estimate
> that about 5 * 17500 pages of pg_largeobject have free space on them
> after a typical day's activity.  That means you need 87500 FSM page
> slots just to keep track of pg_largeobject space, never mind what's
> going on in your user tables.
>
> You didn't say how large your user tables are, or what kind of update
> traffic they see, but I'll bet 100K slots is not near enough for you.
>
> >> 100 is almost certainly too small for max_fsm_relations (we've changed
> >> the default to 1000 as of 7.3.something).  How many active
> >> databases do
> >> you have, and how many user tables?
>
> > In that database cluster, there are 4 databases (template0, template1, pgqv,
> > quickview).  A '\d' for the first three says "No relations", and for the
> > last one lists 17. (15 tables, 1 view, 1 sequence).
>
> Let's see ... in 7.2 there are 30 FSM-able system catalogs per database
> (count the pg_class entries with relkind 'r' or 't').  Ignoring
> template0 which is never vacuumed, you have 105 FSM-able relations in this
> cluster.  I'd suggest bumping up the setting at least a little bit...
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
--
Best Regards,

Mike Benoit
NetNation Communications Inc.
Systems Engineer
Tel: 604-684-6892 or 888-983-6600
 ---------------------------------------

 Disclaimer: Opinions expressed here are my own and not
 necessarily those of my employer