X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 9B4DC8B9D54 for ; Wed, 23 Feb 2005 16:31:11 +0000 (GMT) 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 53690-05 for ; Wed, 23 Feb 2005 16:31:01 +0000 (GMT) Received: from floppy.pyrenet.fr (floppy.pyrenet.fr [194.250.190.2]) by svr1.postgresql.org (Postfix) with ESMTP id 1C3F28B9B19 for ; Wed, 23 Feb 2005 16:31:00 +0000 (GMT) Received: by floppy.pyrenet.fr (Postfix, from userid 106) id ECE9331DDF; Wed, 23 Feb 2005 17:30:59 +0100 (MET) From: Gaetano Mendola X-Newsgroups: pgsql.performance Subject: Re: is pg_autovacuum so effective ? Date: Wed, 23 Feb 2005 17:30:21 +0100 Organization: Hub.Org Networking Services Lines: 56 Message-ID: <421CAF9D.3030407@bigfoot.com> References: <12400.1109107683@sss.pgh.pa.us> <421BD64E.30803@bigfoot.com> <60psyr265t.fsf@dba2.int.libertyrms.com> Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Complaints-To: usenet@news.hub.org To: Christopher Browne User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206) X-Accept-Language: en-us, en In-Reply-To: <60psyr265t.fsf@dba2.int.libertyrms.com> X-Enigmail-Version: 0.90.1.1 X-Enigmail-Supports: pgp-inline, pgp-mime To: pgsql-performance@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.005 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200502/324 X-Sequence-Number: 10653 Christopher Browne wrote: > Gaetano Mendola writes: > > >>Tom Lane wrote: >> >>>Gaetano Mendola writes: >>> >>> >>>>I'm using ony pg_autovacuum. I expect that disk usage will reach >>>>a steady state but is not. PG engine: 7.4.5 >>> >>> >>>One data point doesn't prove that you're not at a steady state. >> >>I do a graph about my disk usage and it's a ramp since one week, >>I'll continue to wait in order to see if it will decrease. >>I was expecting the steady state at something like 4 GB >>( after a full vacuum and reindex ) + 10 % = 4.4 GB >>I'm at 4.6 GB and increasing. I'll see how it will continue. > > > You probably want for the "experiment" to last more than a week. > > After all, it might actually be that with your usage patterns, that > table would stabilize at 15% "overhead," and that might take a couple > or three weeks. > > Unless it's clear that it's growing perilously quickly, just leave it > alone so that there's actually some possibility of reaching an > equilibrium. Any time you "VACUUM FULL" it, that _destroys_ any > experimental results or any noticeable patterns, and it guarantees > that you'll see "seemingly perilous growth" for a while. > > And if the table is _TRULY_ growing "perilously quickly," then it is > likely that you should add in some scheduled vacuums on the table. > Not VACUUM FULLs; just plain VACUUMs. > > I revised cron scripts yet again today to do hourly and "4x/day" > vacuums of certain tables in some of our systems where we know they > need the attention. I didn't schedule any VACUUM FULLs; it's > unnecessary, and would lead directly to system outages, which is > totally unacceptable. Yes, I'm in this direction too. Regards Gaetano Mendola