From: Andy Colson <andy@squeakycode.net>
To: Thom Brown <thom@linux.com>
Cc: Alessandro Gagliardi <alessandro@path.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: set autovacuum=off
Date: Thu, 23 Feb 2012 09:18:35 -0600
Message-ID: <4F4658CB.3030505@squeakycode.net> (raw)
In-Reply-To: <CAA-aLv4KL4atuaqJFi3BBWiRsWiJOVQZMk65JYw=H8ezZV6O8g@mail.gmail.com>
References: <CAAB3BBLsZ94tTzaiTZrdpK+2m1waj8=VL31aPvOGFJ7oAGCHiQ@mail.gmail.com>
<CAA-aLv4KL4atuaqJFi3BBWiRsWiJOVQZMk65JYw=H8ezZV6O8g@mail.gmail.com>
On 2/23/2012 6:34 AM, Thom Brown wrote:
> On 22 February 2012 23:50, Alessandro Gagliardi<alessandro@path.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.
-Andy
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: andy@squeakycode.net, thom@linux.com, alessandro@path.com
Subject: Re: set autovacuum=off
In-Reply-To: <4F4658CB.3030505@squeakycode.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox