Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 48C211337B66 for ; Fri, 19 Nov 2010 21:49:53 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 43391-04 for ; Sat, 20 Nov 2010 01:49:47 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from spinlock.commandprompt.com (host-215.commandprompt.net [207.173.203.215]) by mail.postgresql.org (Postfix) with ESMTP id 742F4133798E for ; Fri, 19 Nov 2010 21:49:45 -0400 (AST) Received: from localhost (localhost [127.0.0.1]) by spinlock.commandprompt.com (Postfix) with ESMTP id 233CE348023; Fri, 19 Nov 2010 17:49:38 -0800 (PST) X-Virus-Scanned: Debian amavisd-new at commandprompt.com Received: from spinlock.commandprompt.com ([127.0.0.1]) by localhost (spinlock.commandprompt.com.commandprompt.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 6-3DDrS95Amf; Fri, 19 Nov 2010 17:49:32 -0800 (PST) Received: from perhan.alvh.no-ip.org (unknown [190.95.24.94]) by spinlock.commandprompt.com (Postfix) with ESMTPSA id DD1B3348022; Fri, 19 Nov 2010 17:49:31 -0800 (PST) Received: by perhan.alvh.no-ip.org (Postfix, from userid 1000) id 7CE646E4BB; Fri, 19 Nov 2010 22:49:25 -0300 (CLST) Content-Type: text/plain; charset=UTF-8 Cc: pgsql-performance Subject: Re: autovacuum blocks the operations of other manual vacuum From: Alvaro Herrera To: kuopo In-reply-to: References: <1289921093-sup-1911@alvh.no-ip.org> Date: Fri, 19 Nov 2010 22:49:25 -0300 Message-Id: <1290217475-sup-5126@alvh.no-ip.org> User-Agent: Sup/git Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9 X-Spam-Level: X-Archive-Number: 201011/323 X-Sequence-Number: 41347 Excerpts from kuopo's message of jue nov 18 04:10:36 -0300 2010: > Hi, > > Thanks for your response. I've checked it again and found that the > main cause is the execution of ANALYZE. As I have mentioned, I have > two tables: table A is a big one (around 10M~100M records) for log > data and table B is a small one (around 1k records) for keeping some > current status. There are a lot of update operations and some search > operations on the table B. For the performance issue, I would like to > keep table B as compact as possible. According your suggestion, I try > to invoke standard vacuum (not full) more frequently (e.g., once per > min). > > However, when I analyze the table A, the autovacuum or vacuum on the > table B cannot find any removable row version (the number of > nonremoveable row versions and pages keeps increasing). After the > analysis finishes, the search operations on the table B is still > inefficient. If I call full vacuum right now, then I can have quick > response time of the search operations on the table B again. Hmm, I don't think we can optimize the analyze-only operation the same way we optimize vacuum (i.e. allow vacuum to proceed while it's in progress). Normally analyze shouldn't take all that long anyway -- why is it that slow? Are you calling it in a transaction that also does other stuff? Are you analyzing more than one table in a single transaction, perhaps even the whole database? Perhaps you could speed it up by lowering vacuum_cost_delay, if it's set to a nonzero value. -- Álvaro Herrera The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support