Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 27348650125 for ; Thu, 4 Dec 2008 10:15:51 -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 46007-08 for ; Thu, 4 Dec 2008 10:15:48 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from wf-out-1314.google.com (wf-out-1314.google.com [209.85.200.174]) by mail.postgresql.org (Postfix) with ESMTP id 91534650123 for ; Thu, 4 Dec 2008 10:15:48 -0400 (AST) Received: by wf-out-1314.google.com with SMTP id 25so4114906wfc.28 for ; Thu, 04 Dec 2008 06:15:46 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to :subject:cc:in-reply-to:mime-version:content-type :content-transfer-encoding:content-disposition:references; bh=HibQ3V8K4TbL7XGxm/jzwr4HKKpvfrhC+K/IkekMPBw=; b=SmrW39eVncNq9FLJqkRTKMHbjmxouKQUoVGp0OOrrLMQaVF46mBUrnltwPdkOXNA5j ayoRlne+c7tTiFsKpgOQhW6ZKOpLulGCwEjQRrJNGV6QkmED8KblWRqBf7tAXYo67X8F o8Pz1GLPxffco718IJ3U1ghf/7SyiTW9Xl0ow= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version :content-type:content-transfer-encoding:content-disposition :references; b=XcNmbj3NBBCWfLzHrOdxR5Umabdxe3kdaLRF/zquLC0GmPWmlhFWCC3LiTGMNAwDN9 y90syplPbcIQF6bX6f9/alky0cfLc7SXaCNLFzoG+SoX5WMHqMI35vbTfYs/h9qyKVs2 Jr3/SOqPN8ACZhPgmgrei7ze9pPpeUj3UPJyI= Received: by 10.114.167.3 with SMTP id p3mr9009228wae.127.1228400146887; Thu, 04 Dec 2008 06:15:46 -0800 (PST) Received: by 10.114.133.4 with HTTP; Thu, 4 Dec 2008 06:15:46 -0800 (PST) Message-ID: Date: Thu, 4 Dec 2008 07:15:46 -0700 From: "Scott Marlowe" To: "AmitKumar Jain" Subject: Re: autovacuum benchmarking ... Cc: pgsql-admin@postgresql.org In-Reply-To: <4937098C.4060605@synechron.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <4937098C.4060605@synechron.com> 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: 200812/25 X-Sequence-Number: 30858 On Wed, Dec 3, 2008 at 3:34 PM, AmitKumar Jain wrote: > Hi Team > > 1. Can we know the how much time will take ongoing vacuum process in DB? > 2. Any benchmarking tool for vacuum process. > 3. Can we know what exact operations has been done by Vacuum process. > 4. I studied that only VACUUM process doesnot lock the table but due to it > insertion in table gets very slow. when i killed vacuum daemon insertion get > faster is it bug in 8.3.3 > > > I know I should not ask all these questions in one go but i need urgently > its answer as vacuum process is just screwing my production database. I > would thankful for any suggestion on above question... At least they're all related, and you're not spamming a half dozen pgsql lists, so really, it's fine to put them all together. 1: Vacuum takes as long as it takes. On faster machines it runs faster. Generally it's very I/O dependent, so lots of hard drives will make it run faster. It's also influenced by how much other activity is going on. Since it doesn't lock, it's not a big deal as long as it finishes in a reasonable amount of time. 2: \timing vacuum tablename; 3: logging, I believe you have to turn log_min_messages down (up?) to get log entries. also look in pg_stat_user_tables. 4: You need to look into the settings autovacuum_vacuum_cost_delay and vacuum_cost_delay. Setting them to 10 or 20 or at most 30 or 40 will slow down vacuum enough that it shouldn't too heavily impact the rest of the system.