X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id E079CD1B172 for ; Sat, 17 Jul 2004 01:28:53 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 85132-09 for ; Sat, 17 Jul 2004 04:28:49 +0000 (GMT) Received: from sccrmhc11.comcast.net (sccrmhc11.comcast.net [204.127.202.55]) by svr1.postgresql.org (Postfix) with ESMTP id E97B4D1B1BE for ; Sat, 17 Jul 2004 01:28:44 -0300 (ADT) Received: from localhost.localdomain (c-67-162-89-61.client.comcast.net[67.162.89.61]) by comcast.net (sccrmhc11) with ESMTP id <2004071704285001100t1nape>; Sat, 17 Jul 2004 04:28:50 +0000 Received: from localhost.localdomain (localhost.localdomain [127.0.0.1]) by localhost.localdomain (8.12.11/8.12.11) with ESMTP id i6H4QbOq014757; Fri, 16 Jul 2004 23:26:37 -0500 Received: (from spike@localhost) by localhost.localdomain (8.12.11/8.12.11/Submit) id i6H4Qb0j014754; Fri, 16 Jul 2004 23:26:37 -0500 X-Authentication-Warning: localhost.localdomain: spike set sender to mike@thegodshalls.com using -f Subject: Re: Disk usage From: mike g Reply-To: mike@thegodshalls.com To: Octavio Alvarez Piza Cc: pgsql-admin@postgresql.org In-Reply-To: <2615.63.84.67.25.1089661406.squirrel@alvarezp.ods.org> References: <2615.63.84.67.25.1089661406.squirrel@alvarezp.ods.org> Content-Type: text/plain Content-Transfer-Encoding: 7bit Message-Id: <1090038396.14010.25.camel@localhost.localdomain> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.5 (1.4.5-7) Date: Fri, 16 Jul 2004 23:26:37 -0500 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.1 tagged_above=0.0 required=5.0 tests=RCVD_IN_SORBS X-Spam-Level: X-Archive-Number: 200407/193 X-Sequence-Number: 14261 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___ are indexes by field (as of reltype = 0). > t__pkey are primary keys indexes. > field names = ext_ 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