pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Octavio Alvarez Piza <alvarezp@alvarezp.ods.org>
To: pgsql-admin@postgresql.org
Subject: Disk usage
Date: Mon, 12 Jul 2004 12:43:26 -0700 (PDT)
Message-ID: <2615.63.84.67.25.1089661406.squirrel@alvarezp.ods.org> (raw)

I have a database to which I re-create daily via ODBC. I only drop the
data, not the index definitions or the data definitions.

I use TRUNCATE to drop the data. I REINDEX each table afterwards.

For some reason, my VACUUM job was not running, so the DB was not being
VACUUMed at all.

Today I ran out of disk space. I could free about 12 MB so I could start
doing VACUUMs. I did a VACUUM and VACUUM FULL in each database in the
cluster. I opened a postgres -O -P and did a REINDEX in each database. I
did a VACUUM and VACUUM FULL once more. Normally, if I dump and re-init,
the database comes down to 150 MB, but currently is taking more than 1.2
GB.

Version is 7.3.2.

Unfortunately, dump/re-init in a short-term is not an option (and
therefore, upgrading isn't either). What else could I try to deflate it?

[root@pgsql data]# find -size +25000 -printf "%p     \t%11s\n";
./base/16977/106415               170672128
./base/16977/106416               170778624
./base/16977/106417                64020480
./base/16977/106418               171925504
./base/16977/106419               149831680
./base/16977/106468               179437568
./base/16977/106486               149012480
./pg_xlog/00000002000000FC         16777216
./pg_xlog/00000002000000FD         16777216
./pg_xlog/0000000300000000         16777216
./pg_xlog/00000002000000F9         16777216
./pg_xlog/00000002000000FA         16777216
./pg_xlog/00000002000000FB         16777216
./pg_xlog/00000002000000FE         16777216

The problem seems not to be on the WAL files.

tbc_bet=# select relname, reltype, relpages from pg_class order by
relpages desc limit 10;
                 relname                 | reltype | relpages
-----------------------------------------+---------+----------
 t_subjects_in_kardex_pkey               |       0 |    21904
 i_t_subjects_in_kardex__ext_group       |       0 |    20987
 i_t_subjects_in_kardex__ext_period      |       0 |    20847
 i_t_subjects_in_kardex__ext_student     |       0 |    20834
 i_t_grades__ext_subject_in_kardex       |       0 |    18290
 t_grades_pkey                           |       0 |    18190
 i_t_subjects_in_kardex__ext_materia     |       0 |     7815
 t_subjects_in_kardex                    |   17051 |      747
 t_students_pkey                         |       0 |      432
 i_t_students__ext_plan                  |       0 |      262
(10 rows)

t_* except t_*_pkey are tables.
i_t_<table-name>__<field-name> are indexes by field (as of reltype = 0).
t_<table-name>_pkey are primary keys indexes.
field names = ext_<table-name> are foreign keys.

(A query involving relfilenode matches the above files with the above
relations)

tbc_bet=# select count(*) from t_subjects_in_kardex;
 count
-------
 84937
(1 row)

tbc_bet=# \d t_subjects_in_kardex;
                                   Table "public.t_subjects_in_kardex"
       Column       |     Type     |                             Modifiers
--------------------+--------------+--------------------------------------------------------------------
 id                 | integer      | not null default
nextval('public.t_subjects_in_kardex_id_seq'::text)
 old_id             | integer      |
 ext_student        | integer      |
 ext_subjet         | integer      |
 ext_period         | integer      |
 ext_group          | integer      |
 final_grade        | integer      |
 tipo               | character(1) |
 eer                | date         |
Indexes: t_subjects_in_kardex_pkey primary key btree (id),
         i_t_subjects_in_kardex__ext_student btree (ext_alumno),
         i_t_subjects_in_kardex__ext_period btree (ext_ciclo),
         i_t_subjects_in_kardex__ext_group btree (ext_grupo),
         i_t_subjects_in_kardex__ext_subject btree (ext_materia),
         i_t_subjects_in_kardex__old_id btree (old_id)
Foreign Key constraints: $1 FOREIGN KEY (ext_student) REFERENCES
t_students(id) ON UPDATE NO ACTION ON DELETE NO ACTION,
                         $2 FOREIGN KEY (ext_period) REFERENCES
t_periods(id) ON UPDATE NO ACTION ON DELETE NO
ACTION,
                         $3 FOREIGN KEY (ext_subject) REFERENCES
t_subjects(id) ON UPDATE NO ACTION ON DELETE NO
ACTION,
                         $4 FOREIGN KEY (ext_group) REFERENCES
t_groups(id) ON UPDATE NO ACTION ON DELETE NO
ACTION

(Field and table names are translated. I tried not to misspell any name.)



view thread (2+ messages)  latest in thread

Message-ID: <2615.63.84.67.25.1089661406.squirrel@alvarezp.ods.org>
Permalink:  ../2615.63.84.67.25.1089661406.squirrel@alvarezp.ods.org/
Also on:    postgresql.org/message-id/2615.63.84.67.25.1089661406.squirrel@alvarezp.ods.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: alvarezp@alvarezp.ods.org
  Subject: Re: Disk usage
  In-Reply-To: <2615.63.84.67.25.1089661406.squirrel@alvarezp.ods.org>
  References: <2615.63.84.67.25.1089661406.squirrel@alvarezp.ods.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox