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

Re: set autovacuum=off

From: Andy Colson <andy(at)squeakycode(dot)net>
To: Thom Brown <thom(at)linux(dot)com>
Cc: Alessandro Gagliardi <alessandro(at)path(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: set autovacuum=off
Date: 2012-02-23 15:18:35
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
On 2/23/2012 6:34 AM, Thom Brown wrote:
> On 22 February 2012 23:50, Alessandro Gagliardi<alessandro(at)path(dot)com>  wrote:
>> I have a database where I virtually never delete and almost never do
>> updates. (The updates might change in the future but for now it's okay to
>> assume they never happen.) As such, it seems like it might be worth it to
>> set autovacuum=off or at least make it so vacuuming hardly ever occurs.
>> Actually, the latter is probably the more robust solution, though I don't
>> know how to do that (hence me writing this list). I did try turning
>> autovacuum off but got:
>> ERROR: parameter "autovacuum" cannot be changed now
>> SQL state: 55P02
>> Not sure what, if anything, I can do about that.
> Autovacuum is controlled by how much of a table has changed, so if a
> table never changes, it never gets vacuumed (with the exceptional case
> being a forced vacuum freeze to mitigate the transaction id
> wrap-around issue).  The settings which control this are
> autovacuum_vacuum_threshold and autovacuum_vacuum_scale_factor.
> Therefore it isn't necessary to disable autovacuum.
> But if you are adamant about disabling it, you need to change it in
> your postgresql.conf file and restart the server.

Agreed, don't disable autovacuum.  It's not that demanding, and if you 
do need it and forget to run it, it might cause you more problems.

I have a db that's on a VM that doesnt get hit very much.  I've noticed 
IO is a little busy (we are talking small percents of percents less than 
one) but still more that I thought should be happening on a db with next 
to no usage.

I found setting autovacuum_naptime = 6min made the IO all but vanish.

And if I ever get a wild hair and blow some stuff away, the db will 
clean up after me.


In response to


pgsql-performance by date

Next:From: Reuven M. LernerDate: 2012-02-23 15:25:46
Subject: Re: Very long deletion time on a 200 GB database
Previous:From: Andrew DunstanDate: 2012-02-23 14:25:13
Subject: Re: Very long deletion time on a 200 GB database

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