Thread: Disk usage
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.)
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@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