Обсуждение: PG 7.2 on Linux: where's the space?

We upgraded from 7.1 to 7.2 recently, and saw the used space on our
database partition drop from something like 80% to under 20% (as
measured by 'du').  Hurrah!  However, it's about four weeks later and
we're back to over 80% again.  Is the solution:

1) More frequent VACUUM FULL operations?

2) REINDEX our biggest tables?

3) Periodicly dump and reload our biggest tables?

I'm really hoping this last one is not the answer, as we'd have to
take the system offline for hours every week to accomplish this.

Jeff Boes wrote:
> We upgraded from 7.1 to 7.2 recently, and saw the used space on our
> database partition drop from something like 80% to under 20% (as
> measured by 'du').  Hurrah!  However, it's about four weeks later and
> we're back to over 80% again.  Is the solution:

This has been discussed a fair bit recently.  I'll try attempt to help,
though I'm sure others are much more qualified.

>
> 1) More frequent VACUUM FULL operations?

Possibly.  However, it's been noted that if you do a lot of deletes
and/or updates so that you have a lot of "dead" tuples, you probably
need to increase your "fsm_*" parameters.  Search the newsgroup archive
for their names for advice on size.

>
> 2) REINDEX our biggest tables?

It has also been noted that indexes can do this too, again a high churn
rate is the cause.  Off the top of my head I don't remember how to find
this out, but it's in the newsgroup archives too.  But I think one of
the things you can do was:

select * from pg_class order by relpages desc;

to try to find out where the space is going.  Search for something like
that.

>
> 3) Periodicly dump and reload our biggest tables?

I hope not! :-)  Increase your fsm_* values and see what that does for
you, before you take more drastic measures.

HTH,
Kevin

>
> I'm really hoping this last one is not the answer, as we'd have to
> take the system offline for hours every week to accomplish this.

On Fri, 2002-08-23 at 14:15, Kevin Brannen wrote:
> Jeff Boes wrote:
> >
> > 1) More frequent VACUUM FULL operations?
>
> Possibly.  However, it's been noted that if you do a lot of deletes
> and/or updates so that you have a lot of "dead" tuples, you probably
> need to increase your "fsm_*" parameters.  Search the newsgroup archive
> for their names for advice on size.
>

Just to clarify, vacuum full recovers *all* possible space in your
database, regardless of FSM settings. It is the regular "lazy" vacuum
that hinges on your FSM. That said, you'll want to lazy vacuum your
highest "churning" tables at least once per filling of your free space
map. You should be able to lazy vacuum with little/no performance hit (I
had one tables getting 2000+ updates a minute that I could vacuum every
5 minutes and had no noticeable impact) When trying to figure out the
frequency, make sure you do vacuum analyze verbose and watch the output,
it will tell you how many tuples you are recovering.

> >
> > 2) REINDEX our biggest tables?
>
> It has also been noted that indexes can do this too, again a high churn
> rate is the cause.  Off the top of my head I don't remember how to find
> this out, but it's in the newsgroup archives too.  But I think one of
> the things you can do was:
>
> select * from pg_class order by relpages desc;
>
> to try to find out where the space is going.  Search for something like
> that.
>

The above query should work well to find your biggest indexes. If you do
rebuild them make sure to get sizes of the indexes before and after you
do it to make sure you're only dropping indexes that are really growing.
BTW - I tend to use a query like this, substituting names and wildcards
where appropriate:

SELECT relname, relkind, relpages, relpages / 128 AS MB FROM
pg_class WHERE relname LIKE 'tablename';

> >
> > 3) Periodicly dump and reload our biggest tables?
>
> I hope not! :-)  Increase your fsm_* values and see what that does for
> you, before you take more drastic measures.
>
> HTH,
> Kevin
>
> >
> > I'm really hoping this last one is not the answer, as we'd have to
> > take the system offline for hours every week to accomplish this.
>

You'll definitely want to modify your fsm before you start do
dump/reloads.  Remember though the first step is to figure out which
tables are doing the churning and how much, you can proceed better from
there.

Robert Treat