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

Re: set autovacuum=off

From: Alessandro Gagliardi <alessandro(at)path(dot)com>
To: Andy Colson <andy(at)squeakycode(dot)net>
Cc: Thom Brown <thom(at)linux(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: set autovacuum=off
Date: 2012-02-23 17:35:07
Message-ID: CAAB3BBK-nzheaG4Ju0snB7b3hqc2pZ+wV6vv9BPt81_k_cLcWw@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
I should have been more clear. I virtually never delete or do updates, but
I insert *a lot*. So the table does change quite a bit, but only in one
direction.

I was unable to disable autovacuum universally (due to the
cant_change_runtime_param error) but I was able to disable it on individual
tables. Still, I know this is heavy handed and sub-optimal. I tried set
autovacuum_naptime='6min' but got the same 55P02 error. Should/can I set
that per table?

I did look at autovacuum_vacuum_threshold and autovacuum_vacuum_scale_**factor
but couldn't make sense out of them. (Besides, I'd probably get the
same 55P02 error if I tried to change them.)

On Thu, Feb 23, 2012 at 7:18 AM, Andy Colson <andy(at)squeakycode(dot)net> wrote:

> 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.
>
> -Andy
>

In response to

Responses

pgsql-performance by date

Next:From: Thom BrownDate: 2012-02-23 17:45:47
Subject: Re: set autovacuum=off
Previous:From: Shaun ThomasDate: 2012-02-23 16:56:22
Subject: Re: : Cost calculation for EXPLAIN output

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