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 116EA5E46FA for ; Fri, 27 Aug 2004 12:32:11 -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 48698-06 for ; Fri, 27 Aug 2004 15:32:13 +0000 (GMT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by svr1.postgresql.org (Postfix) with ESMTP id B0A005E46F5 for ; Fri, 27 Aug 2004 12:32:04 -0300 (ADT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.12.9/8.12.9) with ESMTP id i7RFW3Ls050576 for ; Fri, 27 Aug 2004 15:32:09 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.12.9/8.12.9/Submit) id i7RFAckU043605 for pgsql-admin@postgresql.org; Fri, 27 Aug 2004 15:10:38 GMT From: Chris Browne X-Newsgroups: comp.databases.postgresql.admin Subject: Re: vacuum pg_attribute causes high load Date: Fri, 27 Aug 2004 10:29:39 -0400 Organization: cbbrowne Computing Inc Lines: 68 Message-ID: <60oekwpsbw.fsf@dev6.int.libertyrms.info> References: <5707.1093577361@sss.pgh.pa.us> <20040827130541.14925.qmail@web61307.mail.yahoo.com> Mime-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-Complaints-To: usenet@news.hub.org User-Agent: Gnus/5.110003 (No Gnus v0.3) XEmacs/21.4 (Security Through Obscurity, linux) Cancel-Lock: sha1:jrnrPukdAM8VvBk1s29A1rfBg2o= To: pgsql-admin@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200408/281 X-Sequence-Number: 14676 kristhegambler@yahoo.com (kris pal) writes: > The server is running Postgres 7.4.1 on Linux. > > I tried to do a "vacuum pg_attribute" but its taking a while (I guess because of the size- abt 2GB) and > the load on the server is going very high. Because of this the database server is not responding. So I > killed the psql session. > > 1) Can you tell me if there is any other work around to vacuum the > pg_attribute system table ? Like doing it in parts so that it won't > cause a high load. No, you need to let the vacuum finish. If you stop it early, it'll just increase system load and accomplish _nothing_. > 2) > >   > > Also I want to REINDEX by doing: > > ---------------------------------------------------------------------------- > > drop index pg_attribute_relid_attnam_index; > > vacuum pg_attribute; > > create the index again; > > vacuum analyze pg_attribute; > > ---------------------------------------------------------------------------- > > But I get the : "ERROR:  permission denied: "pg_attribute_relid_attnam_index" is a system catalog" > > * So how can I REINDEX this one ? In order to reindex system tables, you must shut the database down and go in in single user mode. That's a reasonable time to do a VACUUM FULL on pg_attribute; you can be 100% certain that it won't interfere with anything else. > 3) The config file has: > > #max_fsm_pages = 20000          # min max_fsm_relations*16, 6 bytes each > #max_fsm_relations = 1000       # min 100, ~50 bytes each > > So I guess Postgres is using the defaults. Where can I find the > current values or default values. Do you know if I can find some > reading material abt FSM settings and default values? At the end of a VACUUM on everything, FSM information is reported, thus: INFO: free space map: 605 relations, 2039 pages stored; 10896 total pages needed DETAIL: Allocated FSM size: 1000 relations + 30000 pages = 237 kB shared memory. VACUUM The "10896 total pages needed" indicates how much the vacuum needed; my database evidently has things set reasonably appropriately. -- (reverse (concatenate 'string "gro.mca" "@" "enworbbc")) http://cbbrowne.com/info/oses.html "I don't do drugs anymore 'cause I find I get the same effect just by standing up really fast." -- Jonathan Katz