Skip site navigation (1) Skip section navigation (2)

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

From: Costin Grigoras <costing(at)gmail(dot)com>
To: Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: why is autovacuum still ongoing even if set to off ?
Date: 2009-07-31 07:15:24
Message-ID: 4A729A0C.7090603@gmail.com (view raw or flat)
Thread:
Lists: pgsql-general
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?
>   

In response to

pgsql-general by date

Next:From: Jennifer TreyDate: 2009-07-31 09:54:34
Subject: Re: pg_config --sharedir points to the wrong folder.. is this a problem?
Previous:From: Scott MarloweDate: 2009-07-31 06:45:58
Subject: Re: why is autovacuum still ongoing even if set to off ?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group