Skip site navigation (1) Skip section navigation (2)

Re: Disk usage

From: mike g <mike(at)thegodshalls(dot)com>
To: Octavio Alvarez Piza <alvarezp(at)alvarezp(dot)ods(dot)org>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: Disk usage
Date: 2004-07-17 04:26:37
Message-ID: 1090038396.14010.25.camel@localhost.localdomain (view raw or flat)
Thread:
Lists: pgsql-admin
Hi,

It is possible to vacuum each table individually.  Perhaps vacuum the
large tables one a time first and then try a database vacuum.

Mike


On Mon, 2004-07-12 at 14:43, Octavio Alvarez Piza wrote:
> 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(at)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.)
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
>                http://www.postgresql.org/docs/faqs/FAQ.html

In response to

  • Disk usage at 2004-07-12 19:43:26 from Octavio Alvarez Piza

pgsql-admin by date

Next:From: mike gDate: 2004-07-17 04:38:52
Subject: Re: inheritance question
Previous:From: Donald FraserDate: 2004-07-16 17:35:37
Subject: Function to get client IP address

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group