From: | Alvaro Herrera <alvherre(at)commandprompt(dot)com> |
---|---|
To: | kuopo <spkuo(at)cs(dot)nctu(dot)edu(dot)tw> |
Cc: | pgsql-performance <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: autovacuum blocks the operations of other manual vacuum |
Date: | 2010-11-20 01:49:25 |
Message-ID: | 1290217475-sup-5126@alvh.no-ip.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | tv | 2010-11-20 04:43:18 | Re: autovacuum blocks the operations of other manual vacuum |
Previous Message | Robert Klemme | 2010-11-20 00:16:23 | Re: best db schema for time series data? |