Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id A93106325E7 for ; Sat, 1 May 2010 16:19:55 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 65915-03 for ; Sat, 1 May 2010 19:19:48 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-yx0-f195.google.com (mail-yx0-f195.google.com [209.85.210.195]) by mail.postgresql.org (Postfix) with ESMTP id B4B05632EB6 for ; Sat, 1 May 2010 16:19:48 -0300 (ADT) Received: by yxe33 with SMTP id 33so217211yxe.15 for ; Sat, 01 May 2010 12:19:47 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:received:in-reply-to :references:date:message-id:subject:from:to:cc:content-type :content-transfer-encoding; bh=dBANNPBT1pRo1EBIgwtQ2evraHVpFkQGx0dvRInf2Yw=; b=JjD5eTy0kfve4MJC3JvU8vpVMrkR7gwr7GqeGE/e+b07pVL8pZMJCngvzfpefs1J4d /qZCj9wGwpn3+cPoLEUFyMCsFO2TLIFucQ8HzMVylj4uW6ELXx2KnCq+8uXedP5r6UMc 3PEYGvu7n/DGvw30xCZ5w9rh7DR9PbAL+m/0I= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type:content-transfer-encoding; b=X8weTK1myLSm1FeoWO7FCCKfqMwmuC8wYj4Z6fdv9yJ7kYhvpWu2qPAcGqJ/uMqDmr +Tr1j/4cmyBYBKVjh3mRaLJTZQ/PM+uGGSiQp2ukXB9b8/fp5iy73PWFBY/J7egEkf3A obkBkdsLzrdGcy2VRAvfr5rlLil46ehNviM7k= MIME-Version: 1.0 Received: by 10.151.86.2 with SMTP id o2mr5997678ybl.318.1272741587602; Sat, 01 May 2010 12:19:47 -0700 (PDT) Received: by 10.150.134.20 with HTTP; Sat, 1 May 2010 12:19:47 -0700 (PDT) In-Reply-To: References: <65d9d15d-41d2-4bfa-8cc3-6148b4fb9022@z3g2000yqz.googlegroups.com> <4BDB5ECE.4010607@agliodbs.com> Date: Sat, 1 May 2010 13:19:47 -0600 Message-ID: Subject: Re: autovacuum strategy / parameters From: Scott Marlowe To: Robert Haas Cc: Josh Berkus , Rick , pgsql-performance@postgresql.org Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.266 tagged_above=-10 required=5 tests=AWL=0.633, BAYES_00=-1.9, FREEMAIL_FROM=0.001, RCVD_IN_DNSWL_NONE=-0.0001 X-Spam-Level: X-Archive-Number: 201005/11 X-Sequence-Number: 38595 On Sat, May 1, 2010 at 1:17 PM, Scott Marlowe wro= te: > On Sat, May 1, 2010 at 1:08 PM, Robert Haas wrote= : >> On Sat, May 1, 2010 at 12:13 PM, Scott Marlowe = wrote: >>> On Fri, Apr 30, 2010 at 4:50 PM, Josh Berkus wrote: >>>> Which is the opposite of my experience; currently we have several >>>> clients who have issues which required more-frequent analyzes on >>>> specific tables. =A0 Before 8.4, vacuuming more frequently, especially= on >>>> large tables, was very costly; vacuum takes a lot of I/O and CPU. =A0E= ven >>>> with 8.4 it's not something you want to increase without thinking abou= t >>>> the tradeoff >>> >>> Actually I would think that statement would be be that before 8.3 >>> vacuum was much more expensive. =A0The changes to vacuum for 8.4 mostly >>> had to do with moving FSM to disk, making seldom vacuumed tables >>> easier to keep track of, and making autovac work better in the >>> presence of long running transactions. =A0The ability to tune IO load >>> etc was basically unchanged in 8.4. >> >> What about http://www.postgresql.org/docs/8.4/static/storage-vm.html ? > > That really only has an effect no tables that aren't updated very > often. =A0Unless you've got a whole bunch of those, it's not that big of > a deal. sigh, s/ no / on / Anyway, my real point was that the big improvements that made vacuum so much better came in 8.3, with HOT updates and multi-threaded vacuum (that might have shown up in 8.2 even) 8.3 was a huge improvement and compelling upgrade from 8.1 for me.