Re: disk space usage enlarging despite vacuuming

Ron Snyder <snyder(at)roguewave(dot)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