In my experiment, I need about 1~3 min to finish the analyze operation
on the big table (which depends on the value of vacuum_cost_delay). I
am not surprised because this table is a really big one (now, it has
over 200M records).
However, the most of my concerns is the behavior of analyze/vacuum.
You mentioned that the analyze-only operation cannot be optimized as
the same way on optimizing vacuum. Does that mean the analyze
operation on a table would unavoidably affect the vacuum proceeded on
another one? If this is a normal reaction for an analyze operation,
maybe I should try to lower vacuum_cost_delay or use more powerful
hardware to minimize the interfered period. So, the pages for the
small table would not increase quickly.
Do you have any suggestion? Thanks!!
On Sat, Nov 20, 2010 at 9:49 AM, Alvaro Herrera
<alvherre@commandprompt.com> wrote:
> 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
>
