Postgres Pro
Downloads
Home   >   mailing lists

Thread: Disk Usage Problem

Disk Usage Problem

From
Michael Fahey
Date:
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

Re: Disk Usage Problem

From
Tom Lane
Date:
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

auto vacuum settings

From
Jeff Frost
Date:
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

Re: auto vacuum settings

From
Tom Lane
Date:
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

Re: auto vacuum settings

From
Alvaro Herrera
Date:
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"

Re: auto vacuum settings

From
Jeff Frost
Date:
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

Re: auto vacuum settings

From
Alvaro Herrera
Date:
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)