Received: from localhost (unknown [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 9D0BA2E2DA8 for ; Sun, 18 Nov 2007 15:01:02 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 60816-08 for ; Sun, 18 Nov 2007 15:00:54 -0400 (AST) Received: from mail.nordicbet.com (mail.nordicbet.com [193.69.167.130]) by postgresql.org (Postfix) with ESMTP id 54F5F2E2DA2 for ; Sun, 18 Nov 2007 15:00:56 -0400 (AST) Received: by mail.nordicbet.com (Postfix, from userid 1009) id 8142E12CC016; Sun, 18 Nov 2007 20:00:53 +0100 (CET) Date: Sun, 18 Nov 2007 20:00:53 +0100 From: Tobias Brox To: tv@fuzzy.cz Cc: Tobias Brox , =?iso-8859-1?Q?G=E1bor?= Farkas , pgsql-performance@postgresql.org Subject: Re: autovacuum: recommended? Message-ID: <20071118190053.GN1195@mail.nordicbet.com> References: <473D659B.1070008@nekomancer.net> <20071116111355.GB1195@mail.nordicbet.com> <61575.212.24.144.68.1195220303.squirrel@mail.fuzzy.cz> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <61575.212.24.144.68.1195220303.squirrel@mail.fuzzy.cz> Organization: Group Nordicbet User-Agent: Mutt/1.5.9i X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.159 tagged_above=0 required=5 tests=AWL=-0.158, BAYES_50=0.001, SARE_MILLIONSOF=0.315 X-Spam-Level: X-Archive-Number: 200711/258 X-Sequence-Number: 27673 [tv@fuzzy.cz] > The table was quite huge (say 20k of products along with detailed > descriptions etc.) and was completely updated and about 12x each day, i.e. > it qrew to about 12x the original size (and 11/12 of the rows were dead). > This caused a serious slowdown of the application each day, as the > database had to scan 12x more data. The tables we had problems with are transaction-type tables with millions of rows and mostly inserts to the table ... and, eventually some few attributes being updated only on the most recent entries. I tried tuning a lot, but gave it up eventually. Vacuuming those tables took a long time (even if only a very small fraction of the table was touched) and the performance of the inserts to the table was reduced to a level that could not be accepted. By now we've just upgraded the hardware, so it could be worth playing with it again, but our project manager is both paranoid and conservative and proud of it, so I would have to prove that autovacuum is good for us before I'm allowed to turn it on again ;-)