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

Hi,

I'm running 8.4 and I've tried to disable autovacuum since the vacuuming is taken care internally in the code. The same was true with the previous version (8.0 to 8.3) and there was never any problem. However now in 8.4 the "off" setting seems to be ignored even if "track_counts = off" and huge naptime and thresholds are set. For example this is in the logs:

2009-07-30 22:40:25 CEST -  : LOG:  automatic vacuum of table "t.x": index scans: 0
        pages: 0 removed, 43035 remain
        tuples: 0 removed, 6459092 remain
        system usage: CPU 0.73s/0.80u sec elapsed 112.01 sec
2009-07-30 22:43:05 CEST -  : LOG:  automatic vacuum of table "t.y": index scans: 1
        pages: 0 removed, 47527 remain
        tuples: 867656 removed, 6588630 remain
        system usage: CPU 1.07s/4.12u sec elapsed 159.99 sec

Since there are a lot of tables like this (160GB in total) and the insert time is critical we cannot afford having autovacuum messing up the ongoing activity. Is there a way to completely switch it off ?

Btw, the database is one week old, vacuum procedures run anyway often enough (but with large intervals between them so normal operations have a chance to catch up), so there is really no need to have autovacuum doing maintenance operations.

Thanks a lot in advance!

.costin

On Thu, Jul 30, 2009 at 2:52 PM, Costin Grigoras<costing@gmail.com> wrote:
> Hi,
>
> I'm running 8.4 and I've tried to disable autovacuum since the vacuuming is
> taken care internally in the code. The same was true with the previous
> version (8.0 to 8.3) and there was never any problem. However now in 8.4 the
> "off" setting seems to be ignored even if "track_counts = off" and huge
> naptime and thresholds are set. For example this is in the logs:

The only thing I can think of is the automatic kickoff of autovacuum
to prevent transaction wraparound.

The insertion rate is ~250 records / second, quite uniformly spread in
time over ~3000 tables. To reach 2^31 something like 100 days are needed
and the cluster is less than 10 days old. And we do vacuuming :) So as
far as I understand, the wraparound shouldn't happen. In fact before 8.4
we ran for 3 years and never had any problems, so with a fresh
installation there should be even less...

Anyway, if for whatever reason autovacuum kicks in, it should at least
sleep a while between moving to the next table and then it wouldn't be a
major issue. But the configuration option set doesn't seem to include
such an option.

Cheers,

.costin

Scott Marlowe wrote:
> On Thu, Jul 30, 2009 at 2:52 PM, Costin Grigoras<costing@gmail.com> wrote:
>
>> Hi,
>>
>> I'm running 8.4 and I've tried to disable autovacuum since the vacuuming is
>> taken care internally in the code. The same was true with the previous
>> version (8.0 to 8.3) and there was never any problem. However now in 8.4 the
>> "off" setting seems to be ignored even if "track_counts = off" and huge
>> naptime and thresholds are set. For example this is in the logs:
>>
>
> The only thing I can think of is the automatic kickoff of autovacuum
> to prevent transaction wraparound.
>

On Thu, Jul 30, 2009 at 10:56 PM, Costin Grigoras<costing@gmail.com> wrote:
> The insertion rate is ~250 records / second, quite uniformly spread in time
> over ~3000 tables. To reach 2^31 something like 100 days are needed and the
> cluster is less than 10 days old. And we do vacuuming :)

I do believe there are some things that can increment the transaction
count without being an insert.

Also, I believe autovacuum actually logs that it's starting up JUST
because of wraparound, so yeah, that's likely not it now that I've
thought on it.

> Anyway, if for whatever reason autovacuum kicks in, it should at least sleep
> a while between moving to the next table and then it wouldn't be a major
> issue. But the configuration option set doesn't seem to include such an
> option.

Anytime autovacuum is instrusive look at incrementing the nap time to
10 to 20 ms.  If that doesn't help then you're already running on the
ragged edge of IO throughput and might need to look into a faster RAID
array or something.

> Anytime autovacuum is instrusive look at incrementing the nap time to
> 10 to 20 ms.  If that doesn't help then you're already running on the
> ragged edge of IO throughput and might need to look into a faster RAID
> array or something.
>
=> show autovacuum_naptime;
 autovacuum_naptime
--------------------
 100h

=> show autovacuum_freeze_max_age ;
 autovacuum_freeze_max_age
---------------------------
 200000000

=> show autovacuum_vacuum_threshold ;
 autovacuum_vacuum_threshold
-----------------------------
 500000

=> 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 ?

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?!

07/31/2009 08:10:07 AM
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           1.38    0.00    0.81    0.10    0.00   97.71

Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s
avgrq-sz avgqu-sz   await  svctm  %util
cciss/c0d0        0.00   706.20    0.00  133.00     0.00  3356.80
50.48     0.29    2.17   0.08   1.00
cciss/c1d0        0.00     2.80    6.80   63.20   502.40   669.70
33.49     0.08    1.20   0.49   3.40

On Fri, Jul 31, 2009 at 12:24 AM, Costin Grigoras<costing@gmail.com> wrote:
>
>> Anytime autovacuum is instrusive look at incrementing the nap time to
>> 10 to 20 ms.  If that doesn't help then you're already running on the
>> ragged edge of IO throughput and might need to look into a faster RAID
>> array or something.
>>
>
> => show autovacuum_naptime;
> autovacuum_naptime
> --------------------
> 100h

Dangit, I meant cost delay...  urg.  That's a really long naptime, but
since you vacuum anyway it shouldn't matter.

So, after disabling autovacuum in postgresql.conf, you reloaded /
restart postgresql, right?

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?

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?
>