why is autovacuum still ongoing even if set to off ?

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