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 D696856394 for ; Thu, 24 Feb 2005 10:59:31 +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 80141-04 for ; Thu, 24 Feb 2005 10:59:28 +0000 (GMT) Received: from floppy.pyrenet.fr (floppy.pyrenet.fr [194.250.190.2]) by svr1.postgresql.org (Postfix) with ESMTP id D2E5955456 for ; Thu, 24 Feb 2005 10:59:25 +0000 (GMT) Received: by floppy.pyrenet.fr (Postfix, from userid 106) id 1DE7431DE7; Thu, 24 Feb 2005 11:59:33 +0100 (MET) From: Gaetano Mendola X-Newsgroups: pgsql.performance Subject: Re: is pg_autovacuum so effective ? Date: Thu, 24 Feb 2005 11:58:51 +0100 Organization: Hub.Org Networking Services Lines: 68 Message-ID: <421DB36B.70004@bigfoot.com> References: <12400.1109107683@sss.pgh.pa.us> <421BD64E.30803@bigfoot.com> <60psyr265t.fsf@dba2.int.libertyrms.com> <421CD6F0.8010406@zeut.net> Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Complaints-To: usenet@news.hub.org To: "Matthew T. O'Connor" User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206) X-Accept-Language: en-us, en In-Reply-To: <421CD6F0.8010406@zeut.net> 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/348 X-Sequence-Number: 10677 Matthew T. O'Connor wrote: > Christopher Browne wrote: > >> Gaetano Mendola writes: >> >> >>> 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. >> >> > > Chris, is this in addition to pg_autovacuum? Or do you not use > pg_autovacuum at all?, and if so why not? I have the same requirement too. Actually pg_autovacuum can not be instructed "per table" so some time the global settings are not good enough. I have a table of logs with 6 milions rows ( 3 years logs ) I insert on that page ~ 6000 rows for day. I'm running pg_autovacuum with setting to ANALYZE or VACUUM table if the 10% is touched. With this setting pg_autovacuum will analyze that table each 3 months!!! So I need to analyze and/or vacuum it manually. Regards Gaetano Mendola