[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    Re: [GENERAL] why is autovacuum still ongoing even if set to off
From:       Costin Grigoras <costing () gmail ! com>
Date:       2009-07-31 7:15:24
Message-ID: 4A729A0C.7090603 () gmail ! com
[Download RAW message or body]

Ok, if autovacuum doesn't actually lock the tables for this long time 
then it's fine. But I started investigating this because the inserts 
were hanging and I'm still puzzled why it's active when it shouldn't be :)

In principle you are right, but in this particular case autovacuum gets 
in the way since internal cleanup procedures already do 
cluster/vacuum/analyze when needed.

Anyway, now the autovacuum thread is gone so ... I'll investigate more 
next time when it pops up.

Thanks a lot for your help!

Cheers,

.costin

Scott Marlowe wrote:
> On Fri, Jul 31, 2009 at 12:24 AM, Costin Grigoras<costing@gmail.com> wrote:
>
>   
>> => show autovacuum;
>> autovacuum
>> ------------
>> off
>>
>> I really overdid it, I know, but this issue is very annoying. Even with
>> these settings it's still ongoing.
>>
>> The IO doesn't seem to be an issue, the DB sits on a RAID6 of 8 10K rpm
>> disks while the logs are on another identical controller, 512M cache on
>> each. 32G of RAM with 30 being only cache also help. fsync is off and below
>> is the output of iostat. But something is indeed weird, if autovacuum
>> doesn't consume CPU and doesn't do much IO then where does it spend the time
>>     
>
> sleeping so as not to get in the way.
>
>   
>> Btw, I've tried "vacuum full analyze" on the same table that autovacuum
>> reports vacuumed in 542 seconds and it only took 36 seconds on the command
>> line (when the files were not in cache, 7 seconds when they were)! And I see
>> the vacuum process using a lot of cpu in "top" while the autovacuum process
>> is always at 2-3% only. This raises another question, why is autovacuum
>> taking so long to do much less?!
>>     
>
> What's the difference between autovacuum_vacuum_cost_delay and
> vacuum_cost_delay ?
>
> Since autovacuum seems to not be causing any actual problems, why are
> you so invested in turning it off instead of making sure it's tuned to
> not get in the way?
>   

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[prev in list] [next in list] [prev in thread] [next in thread]