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.
Any suggestions for this situation?
On Tue, Nov 16, 2010 at 11:26 PM, Alvaro Herrera
<alvherre@commandprompt.com> wrote:
> Excerpts from kuopo's message of vie nov 12 05:01:24 -0300 2010:
>> Hi,
>>
>> I have a question about the behavior of autovacuum. When I have a big
>> table A which is being processed by autovacuum, I also manually use
>> (full) vacuum to clean another table B. Then I found that I always got
>> something like “found 0 removable, 14283 nonremovable row”. However,
>> if I stop the autovacuum functionality and use vacuum on that big
>> table A manually, I can clean table B (ex. found 22615 removable, 2049
>> nonremovable row).
>>
>> Is this correct? Why do vacuum and autovacuum have different actions?
>
> Vacuum full does not assume that it can clean up tuples while other
> transactions are running, and that includes the (non full, or "lazy")
> vacuum that autovacuum is running. Autovacuum only runs lazy vacuum;
> and that one is aware that other concurrent vacuums can be ignored.
>
> Just don't use vacuum full unless strictly necessary. It has other
> drawbacks.
>
> --
> Álvaro Herrera <alvherre@commandprompt.com>
> The PostgreSQL Company - Command Prompt, Inc.
> PostgreSQL Replication, Consulting, Custom Development, 24x7 support
>