From: Alvaro Herrera <alvherre@commandprompt.com>
To: kuopo <spkuo@cs.nctu.edu.tw>
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: autovacuum blocks the operations of other manual vacuum
Date: Fri, 19 Nov 2010 22:49:25 -0300
Message-ID: <1290217475-sup-5126@alvh.no-ip.org> (raw)
In-Reply-To: <AANLkTimYQ40v+ryYFRkQ7ENdwuQOxGUv3j7sLLjDwd38@mail.gmail.com>
References: <AANLkTimNrG6JA6MbMb52T5CVt+hi_hsD_fW6NRDkARgH@mail.gmail.com>
<1289921093-sup-1911@alvh.no-ip.org>
<AANLkTimYQ40v+ryYFRkQ7ENdwuQOxGUv3j7sLLjDwd38@mail.gmail.com>
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 <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: alvherre@commandprompt.com, spkuo@cs.nctu.edu.tw
Subject: Re: autovacuum blocks the operations of other manual vacuum
In-Reply-To: <1290217475-sup-5126@alvh.no-ip.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox