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 67980D1B243 for ; Mon, 2 Aug 2004 13:48:18 -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 10991-04 for ; Mon, 2 Aug 2004 16:48:13 +0000 (GMT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by svr1.postgresql.org (Postfix) with ESMTP id F0E98D1B1F8 for ; Mon, 2 Aug 2004 13:48:12 -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 i72GmBqf020684 for ; Mon, 2 Aug 2004 16:48:12 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.12.9/8.12.9/Submit) id i72GdUZt019393 for pgsql-admin@postgresql.org; Mon, 2 Aug 2004 16:39:30 GMT From: Gaetano Mendola X-Newsgroups: comp.databases.postgresql.admin Subject: Re: running only pg_autovacuum for one week Date: Mon, 02 Aug 2004 18:39:01 +0200 Organization: PYRENET Midi-pyrenees Provider Lines: 42 Message-ID: <410E6E25.9010108@bigfoot.com> References: <410E3CCA.9090805@bigfoot.com> <1091463170.27166.35.camel@localhost.localdomain> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Complaints-To: abuse@pyrenet.fr To: Scott Marlowe User-Agent: Mozilla Thunderbird 0.7.1 (Windows/20040626) X-Accept-Language: en-us, en In-Reply-To: <1091463170.27166.35.camel@localhost.localdomain> X-Enigmail-Version: 0.84.2.0 X-Enigmail-Supports: pgp-inline, pgp-mime 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/8 X-Sequence-Number: 14403 Scott Marlowe wrote: > On Mon, 2004-08-02 at 07:08, Gaetano Mendola wrote: > >>-----BEGIN PGP SIGNED MESSAGE----- >>Hash: SHA1 >> >>Hi all, >>I did the experiment of running only the autovacuum for one week without >>running my daily autovacuum full and reindex on heavy updated/inserted >>tables. >>Yesterday I reenabled the vacuum full and reindex and, as you can see >>from the attachment, I recover 600MB of wasted space. > > > autovacuum is a daemon. You don't run it once a week, you set it loose > and forget about it. Unless you're running it then shutting it down, > running once a week is unnecessary. Sorry, I meant that I had running "only" the pg_autovacuum for the entire week, without run also my vacuum full + reindex once a day. I was only stopping it and rerunning for logrotation purpose. Analyzing the graph on Wednedsay I had a few of processes in "idle in transaction" state and as you can see the graph had a big ramp and that space was not reclamed till this morning :-( > Also, what are you fsm settings in the postgresql.conf file? At the end of my autovacuum full I have: INFO: free space map: 603 relations, 38202 pages stored; 40592 total pages needed DETAIL: Allocated FSM size: 1000 relations + 2000000 pages = 11780 kB shared memory. So I think that I'm not reaching the limits. As explained in another thread untill I can not set the "threasholds" per table the autovacuum is useless ( milions rows tables with hundred of insert per day ). Regards Gaetano Mendola