Обсуждение: Disk Usage Problem

My database cluster at 22 GB is outgrowing the 30 GB partition I set
aside for it.

I periodically dump the cluster to a development server, but when it is
loaded onto the development server, the cluster takes up just 145MB.

I use temporary tables fairly extensively in a key part of the application.

Here are the biggest tables in the production system:

             relname             | relfilenode | relpages
---------------------------------+-------------+----------
 pg_attribute_relid_attnam_index |       16609 |  2519264
 pg_attribute_relid_attnum_index |       16610 |    98014
 pg_class_relname_nsp_index      |       16614 |    25220


I'm using 7.4.7-6sarge1 . I vacuum nightly and do a full vacuum weekly,
but this doesn't seem to have any effect on size.



TIA

Michael

Michael Fahey <mfahey@winklerpartners.com> writes:
> My database cluster at 22 GB is outgrowing the 30 GB partition I set
> aside for it.

> I periodically dump the cluster to a development server, but when it is
> loaded onto the development server, the cluster takes up just 145MB.

> I'm using 7.4.7-6sarge1 . I vacuum nightly and do a full vacuum weekly,
> but this doesn't seem to have any effect on size.

It's clear that you aren't vacuuming *nearly* enough for your workload.
Think about vacuuming hourly.  Check your FSM settings.

            regards, tom lane

Since we're on the vacuum kick these days, what's the best way to tell if my
autovacuum settings are working well?  I'm sure I can query some info out of
the pg_stats tables, no?

Also, if using autovacuum, should I still do a vacuum full on a regular
basis?

--
Jeff Frost, Owner     <jeff@frostconsultingllc.com>
Frost Consulting, LLC     http://www.frostconsultingllc.com/
Phone: 650-780-7908    FAX: 650-649-1954

Jeff Frost <jeff@frostconsultingllc.com> writes:
> Since we're on the vacuum kick these days, what's the best way to tell if my
> autovacuum settings are working well?  I'm sure I can query some info out of
> the pg_stats tables, no?

If the database isn't bloating (ie, physical storage growing faster than
valid data volume) then it's getting the job done.  Keep an eye on
"du $PGDATA" or pg_class.relpages entries.

> Also, if using autovacuum, should I still do a vacuum full on a regular
> basis?

No.  Needing to do a VACUUM FULL would be prima facie evidence that
autovacuum *isn't* getting its job done.

            regards, tom lane

On Wed, Aug 31, 2005 at 09:47:35AM -0400, Tom Lane wrote:
> Jeff Frost <jeff@frostconsultingllc.com> writes:
> > Since we're on the vacuum kick these days, what's the best way to tell if my
> > autovacuum settings are working well?  I'm sure I can query some info out of
> > the pg_stats tables, no?
>
> If the database isn't bloating (ie, physical storage growing faster than
> valid data volume) then it's getting the job done.  Keep an eye on
> "du $PGDATA" or pg_class.relpages entries.

Another test may be checking table stats with pgstattuple.  If the
percentage of dead rows grows as time passes, you have a problem.

--
Alvaro Herrera <alvherre[]alvh.no-ip.org>      Architect, www.EnterpriseDB.com
"Puedes vivir solo una vez, pero si lo haces bien, una vez es suficiente"

Thanks Tom!

Now what does the relpages column mean?  I.e. how should I interpret that
entry for my tables?

On Wed, 31 Aug 2005, Tom Lane wrote:

> If the database isn't bloating (ie, physical storage growing faster than
> valid data volume) then it's getting the job done.  Keep an eye on
> "du $PGDATA" or pg_class.relpages entries.

--
Jeff Frost, Owner     <jeff@frostconsultingllc.com>
Frost Consulting, LLC     http://www.frostconsultingllc.com/
Phone: 650-780-7908    FAX: 650-649-1954

On Wed, Aug 31, 2005 at 10:59:49AM -0700, Jeff Frost wrote:
> Thanks Tom!
>
> Now what does the relpages column mean?  I.e. how should I interpret that
> entry for my tables?

relpages is the number of "pages" of the table.  A page is 8kb (unless
you changed a compile-time setting -- you can check it with SHOW
block_size).

Each page stores whole tuples, and can have some amount of free space.
Tuples exceeding a certain length will be "toasted", that is, some of
its attributes will be moved to an external table.  Check the
documentation for more details -- there is a section called "Disk
Storage" or something similar.

--
Alvaro Herrera -- Valdivia, Chile         Architect, www.EnterpriseDB.com
"[PostgreSQL] is a great group; in my opinion it is THE best open source
development communities in existence anywhere."                (Lamar Owen)