X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 894639DCA15 for ; Sat, 11 Feb 2006 22:09:17 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 71268-09 for ; Sat, 11 Feb 2006 22:09:21 -0400 (AST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by postgresql.org (Postfix) with ESMTP id 8EE5F9DC94C for ; Sat, 11 Feb 2006 22:09:15 -0400 (AST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.13.1/8.13.1) with ESMTP id k1C29C3s033930 for ; Sun, 12 Feb 2006 02:09:12 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.13.1/8.13.1/Submit) id k1C23td1033456 for pgsql-admin@postgresql.org; Sat, 11 Feb 2006 22:03:55 -0400 (AST) (envelope-from news) From: Chris Browne X-Newsgroups: pgsql.admin Subject: Re: How to VACUUM this table? "998994633 estimated total rows" Date: Sat, 11 Feb 2006 20:57:47 -0500 Organization: cbbrowne Computing Inc Lines: 37 Message-ID: <60ek29e3tw.fsf@dba2.int.libertyrms.com> References: <43ED56D3.7040404@mediaroot.de> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Complaints-To: usenet@news.hub.org User-Agent: Gnus/5.1007 (Gnus v5.10.7) XEmacs/21.4.18 (linux) Cancel-Lock: sha1:LPgg6zCuwL/uJAhnKeb+Kg6it1M= To: pgsql-admin@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=-1.276 required=5 tests=[ALL_TRUSTED=-1.44, AWL=0.164] X-Spam-Score: -1.276 X-Spam-Level: X-Archive-Number: 200602/151 X-Sequence-Number: 20572 an@mediaroot.de (Aldor) writes: > I'm just curious about a VACUUM plan of this table: > > 1st) When to do VAUUM? > 2nd) How often to do VACUUM? > 3rd) With which postgresql.conf paremeters to set up vacuum? > 4th) When to do a VACUUM FULL? > 5th) Can autovacuum of 8.1 be used in this case? > > I'm a little bit afraid about the size of the table, but I think > somebody should have a solution... > > Here is the complete ANALYZE output of the table: > > INFO: "tbl1": scanned 300 of 27744713 pages, containing 10802 live rows > and 0 dead rows; 300 rows in sample, 998994633 estimated total rows > > The size of the data is 340 GB, this are 40% of the disk-array. Vacuuming this table is likely to take a rather long time. Hours and hours; possibly multiple days. I don't think you'll *ever* want to VACUUM FULL this table; I'm not sure you ever want autovacuum to process it either. I instead think you want to choose a time which seems best to start a Very Long Transaction to issue a VACUUM ANALYZE on it. If you are quite sure it has few if any dead tuples, it might be something to try to avoid VACUUMing except as needed to evade the 2^31 transaction limit... I am not sure that's the only opinion you ought to consider on it... -- (reverse (concatenate 'string "moc.enworbbc" "@" "enworbbc")) http://cbbrowne.com/info/sap.html Why are there interstate highways in Hawaii?