> 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