X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 222FBD85DC for ; Tue, 18 Oct 2005 13:48:13 -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 05837-09 for ; Tue, 18 Oct 2005 16:48:11 +0000 (GMT) Received: from smtp102.rog.mail.re2.yahoo.com (smtp102.rog.mail.re2.yahoo.com [206.190.36.80]) by svr1.postgresql.org (Postfix) with SMTP id 269F7D82C2 for ; Tue, 18 Oct 2005 13:48:10 -0300 (ADT) Received: (qmail 42994 invoked from network); 18 Oct 2005 16:48:07 -0000 Received: from unknown (HELO phlogiston.dydns.org) (a.sullivan@rogers.com@209.222.54.227 with login) by smtp102.rog.mail.re2.yahoo.com with SMTP; 18 Oct 2005 16:48:07 -0000 Received: by phlogiston.dydns.org (Postfix, from userid 1000) id 0F4DA40E6; Tue, 18 Oct 2005 12:48:06 -0400 (EDT) Date: Tue, 18 Oct 2005 12:48:06 -0400 From: Andrew Sullivan To: pgsql-performance@postgresql.org Subject: Re: Help tuning postgres Message-ID: <20051018164806.GH3441@phlogiston.dyndns.org> References: <1129128315.2995.174.camel@coppola.muc.ecircle.de> <1129136130.2995.188.camel@coppola.muc.ecircle.de> <20051012220036.GE13571@phlogiston.dyndns.org> <1129191303.2995.192.camel@coppola.muc.ecircle.de> <20051013124007.GA15592@phlogiston.dyndns.org> <1129648896.27587.22.camel@coppola.muc.ecircle.de> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <1129648896.27587.22.camel@coppola.muc.ecircle.de> User-Agent: Mutt/1.5.9i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.033 required=5 tests=[AWL=-0.017, FORGED_RCVD_HELO=0.05] X-Spam-Level: X-Archive-Number: 200510/265 X-Sequence-Number: 15033 On Tue, Oct 18, 2005 at 05:21:37PM +0200, Csaba Nagy wrote: > INFO: vacuuming "public.some_table" > INFO: "some_table": removed 29598 row versions in 452 pages > DETAIL: CPU 0.01s/0.04u sec elapsed 18.77 sec. > INFO: "some_table": found 29598 removable, 39684 nonremovable row > versions in 851 pages > DETAIL: 0 dead row versions cannot be removed yet. > Does that mean that 39684 nonremovable pages are actually the active > live pages in the table (as it reports 0 dead) ? I'm sure I don't have > any long running transaction, at least according to pg_stats_activity > (backed by the linux ps too). Or I should run a vacuum full... > > This table is one of which has frequently updated rows. No, you should be ok there. What that should tell you is that you have about 40,000 rows in the table. But notice that your vacuum process just removed about 75% of the live table rows. Moreover, your 39684 rows are taking 851 pages. On a standard installation, that's usually 8Kb/page. So that's about 6,808 Kb of physical storage space you're using. Is that consistent with the size of your data? If it's very large compared to the data you have stored in there, you may want to ask if you're "leaking" space from the free space map (because of that table turnover, which seems pretty severe). A -- Andrew Sullivan | ajs@crankycanuck.ca The whole tendency of modern prose is away from concreteness. --George Orwell