From: | kuopo <spkuo(at)cs(dot)nctu(dot)edu(dot)tw> |
---|---|
To: | Alvaro Herrera <alvherre(at)commandprompt(dot)com> |
Cc: | pgsql-performance <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: autovacuum blocks the operations of other manual vacuum |
Date: | 2010-11-18 07:10:36 |
Message-ID: | AANLkTimYQ40v+ryYFRkQ7ENdwuQOxGUv3j7sLLjDwd38@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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(at)commandprompt(dot)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(at)commandprompt(dot)com>
> The PostgreSQL Company - Command Prompt, Inc.
> PostgreSQL Replication, Consulting, Custom Development, 24x7 support
>
From | Date | Subject | |
---|---|---|---|
Next Message | Uwe Bartels | 2010-11-18 10:10:36 | executor stats / page reclaims |
Previous Message | Pavel Stehule | 2010-11-18 06:14:24 | Re: Query Performance SQL Server vs. Postgresql |