Re: autovacuum blocks the operations of other manual vacuum

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(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support