Re: why is autovacuum still ongoing even if set to off ?

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(at)gmail(dot)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?
>