Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 2951963309E for ; Fri, 13 Feb 2009 21:08:17 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 91332-08 for ; Fri, 13 Feb 2009 21:08:13 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-gx0-f224.google.com (mail-gx0-f224.google.com [209.85.217.224]) by mail.postgresql.org (Postfix) with ESMTP id 81363632A3E for ; Fri, 13 Feb 2009 21:08:13 -0400 (AST) Received: by gxk24 with SMTP id 24so1616456gxk.19 for ; Fri, 13 Feb 2009 17:08:12 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :date:message-id:subject:from:to:cc:content-type :content-transfer-encoding; bh=7T8jqW7xu/9t8kVPJNRIrN8DPCBfIX1xKybH2fnFaew=; b=jvL6gRQKzLA/Qnxq5RIso94A4NiOLrflIpCHCngpBgIKtKT6RTxMgMTuRUkld0Utbl 1rdmHTy0arwYEmF1FBu9rcrVLhjtXm1Lf1SGWI2odylwPCIxPzyDDpE9p9HSYfm5ttXe AOAulbMn++GnGiiQGVK0/32zkbavc1gJ46nFA= 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=lJTxmBImczggiibn3JaiNZpeANSiXRaiJtUz5Usmzk+w93w8IjbmrmegeBW7dUk+na UFISrquZi2nrrSSAngfwRPhgc/JRdeP9gdmI4FqkxZA0pM9HymbyoVOTUJL+Umfb+/c3 fEZv8Px6AgJH/nJSeoNYyP0wq2Nlzye/8ucgM= MIME-Version: 1.0 Received: by 10.150.202.9 with SMTP id z9mr1081805ybf.181.1234573692138; Fri, 13 Feb 2009 17:08:12 -0800 (PST) In-Reply-To: <200902140102.09224@zmi.at> References: <4995ABC0.4090401@musicreports.com> <200902140102.09224@zmi.at> Date: Fri, 13 Feb 2009 18:08:12 -0700 Message-ID: Subject: Re: Vacuum wait time problem From: Scott Marlowe To: Michael Monnerie Cc: pgsql-admin@postgresql.org Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200902/96 X-Sequence-Number: 31317 On Fri, Feb 13, 2009 at 5:02 PM, Michael Monnerie wrote: > On Freitag 13 Februar 2009 Roger Ging wrote: >> I'm running vacuum full analyze verbose on a table with 20million >> rows and 11 indexes. In top, I'm seeing [pdflush] and postgres: >> writer process each using diferent cpu cores, with wait time well >> above 90% on each of them. The vacuum has been running for several >> hours > > Roger, I've had the same issue some time ago and wondered why it was so > slow. I did "iostat -kx 5 555" and saw that I/O was also quite low. > > vacuum_cost_delay = 0 > That was the trick for me. It was set to 250(ms), where it took 5 hours > for a vacuum to run. Now it takes 5-15 minutes. Wow!!! 250 ms is HUGE in the scheme of vacuum cost delay. even 10ms is usually plenty to slow down vacuum enough to keep it out of your way and double to quadruple your vacuum times. 250 is like taking a nap every 5 feet while running a mile. :)