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@xxxxxxxxx> 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?
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
