Horton
Menu

Thread

  1. Disable autovacuum guc?

    Joshua D. Drake <jd@commandprompt.com> — 2016-10-20T01:27:20Z

    Hello,

After all these years, we are still regularly running into people who 
say, "performance was bad so we disabled autovacuum". I am not talking 
about once in a while, it is often. I would like us to consider removing 
the autovacuum option. Here are a few reasons:

1. It does not hurt anyone
2. It removes a foot gun
3. Autovacuum is *not* optional, we shouldn't let it be
4. People could still disable it at the table level for those tables 
that do fall into the small window of, no maintenance is o.k.
5. People would still have the ability to decrease the max_workers to 1 
(although I could argue about that too).

Sincerely,

JD
-- 
Command Prompt, Inc.                  http://the.postgres.company/
                         +1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.

  2. Re: Disable autovacuum guc?

    Josh Berkus <josh@agliodbs.com> — 2016-10-20T02:22:52Z

    On 10/19/2016 06:27 PM, Joshua D. Drake wrote:
> Hello,
> 
> After all these years, we are still regularly running into people who
> say, "performance was bad so we disabled autovacuum". I am not talking
> about once in a while, it is often. I would like us to consider removing
> the autovacuum option. Here are a few reasons:
> 
> 1. It does not hurt anyone
> 2. It removes a foot gun
> 3. Autovacuum is *not* optional, we shouldn't let it be
> 4. People could still disable it at the table level for those tables
> that do fall into the small window of, no maintenance is o.k.
> 5. People would still have the ability to decrease the max_workers to 1
> (although I could argue about that too).

People who run data warehouses where all of the data comes in as batch
loads regularly disable autovacuum, and should do so.  For the DW/batch
load use-case, it makes far more sense to do batch loads interspersed
with ANALYZEs and VACUUMS of loaded/updated tables.

-- 
--
Josh Berkus
Red Hat OSAS
(any opinions are my own)

  3. Re: Disable autovacuum guc?

    Craig Ringer <craig@2ndquadrant.com> — 2016-10-20T02:55:06Z

    On 20 October 2016 at 09:27, Joshua D. Drake <jd@commandprompt.com> wrote:
> Hello,
>
> After all these years, we are still regularly running into people who say,
> "performance was bad so we disabled autovacuum". I am not talking about once
> in a while, it is often. I would like us to consider removing the autovacuum
> option. Here are a few reasons:
>
> 1. It does not hurt anyone

I disagree.

It's important for a number of tests, and can be very useful when
doing diagnostic work. It's also important for some kinds of data
recovery efforts.

However, I do think the documentation needs to loudly warn users not
to turn it off, and that if they think vacuum is slowing their system
down they probably actually have to make it run MORE. The problem is
people not understanding autovac, and taking away the option to turn
it off won't help. They'll just find other ways to cripple it.

-- 
 Craig Ringer                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

  4. Re: Disable autovacuum guc?

    Michael Paquier <michael.paquier@gmail.com> — 2016-10-20T03:04:14Z

    On Thu, Oct 20, 2016 at 11:55 AM, Craig Ringer <craig@2ndquadrant.com> wrote:
> On 20 October 2016 at 09:27, Joshua D. Drake <jd@commandprompt.com> wrote:
>> 1. It does not hurt anyone
>
> I disagree.
>
> It's important for a number of tests, and can be very useful when
> doing diagnostic work. It's also important for some kinds of data
> recovery efforts.

+1. From time to time, it is really helpful to disable it for
development purposes.
-- 
Michael

  5. Re: Disable autovacuum guc?

    Joshua D. Drake <jd@commandprompt.com> — 2016-10-20T13:34:42Z

    On 10/19/2016 07:22 PM, Josh Berkus wrote:
> On 10/19/2016 06:27 PM, Joshua D. Drake wrote:
>> Hello,
>>
>> After all these years, we are still regularly running into people who
>> say, "performance was bad so we disabled autovacuum". I am not talking
>> about once in a while, it is often. I would like us to consider removing
>> the autovacuum option. Here are a few reasons:
>>
>> 1. It does not hurt anyone
>> 2. It removes a foot gun
>> 3. Autovacuum is *not* optional, we shouldn't let it be
>> 4. People could still disable it at the table level for those tables
>> that do fall into the small window of, no maintenance is o.k.
>> 5. People would still have the ability to decrease the max_workers to 1
>> (although I could argue about that too).
>
> People who run data warehouses where all of the data comes in as batch
> loads regularly disable autovacuum, and should do so.  For the DW/batch
> load use-case, it makes far more sense to do batch loads interspersed
> with ANALYZEs and VACUUMS of loaded/updated tables.

Hrm, true although that is by far a minority of our users. What if we 
made it so we disabled the autovacuum guc but made it so you could 
disable autovacuum per database (ALTER DATABASE SET or something such 
thing?).

Sincerely,

JD


-- 
Command Prompt, Inc.                  http://the.postgres.company/
                         +1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.

  6. Re: Disable autovacuum guc?

    Josh Berkus <josh@agliodbs.com> — 2016-10-20T15:54:43Z

    On 10/20/2016 06:34 AM, Joshua D. Drake wrote:
> On 10/19/2016 07:22 PM, Josh Berkus wrote:
>> On 10/19/2016 06:27 PM, Joshua D. Drake wrote:
>>> Hello,
>>>
>>> After all these years, we are still regularly running into people who
>>> say, "performance was bad so we disabled autovacuum". I am not talking
>>> about once in a while, it is often. I would like us to consider removing
>>> the autovacuum option. Here are a few reasons:
>>>
>>> 1. It does not hurt anyone
>>> 2. It removes a foot gun
>>> 3. Autovacuum is *not* optional, we shouldn't let it be
>>> 4. People could still disable it at the table level for those tables
>>> that do fall into the small window of, no maintenance is o.k.
>>> 5. People would still have the ability to decrease the max_workers to 1
>>> (although I could argue about that too).
>>
>> People who run data warehouses where all of the data comes in as batch
>> loads regularly disable autovacuum, and should do so.  For the DW/batch
>> load use-case, it makes far more sense to do batch loads interspersed
>> with ANALYZEs and VACUUMS of loaded/updated tables.
> 
> Hrm, true although that is by far a minority of our users. What if we
> made it so we disabled the autovacuum guc but made it so you could
> disable autovacuum per database (ALTER DATABASE SET or something such
> thing?).

Well, that wouldn't fix the problem; people would just disable it per
database, even if it was a bad idea.

If I can't get rid of vacuum_defer_cleanup_age, you're not going to be
able to get rid of autovacuum.

Now, if you want to "fix" this issue, one thing which would help a lot
is making it possible to disable/enable Autovacuum on one table without
exclusive locking (for the ALTER statement), and create a how-to doc
somewhere which explains how and why to disable autovac per-table.  Most
of our users don't know that it's even possible to adjust this per-table.


-- 
--
Josh Berkus
Red Hat OSAS
(any opinions are my own)

  7. Re: Disable autovacuum guc?

    Joshua D. Drake <jd@commandprompt.com> — 2016-10-20T16:05:00Z

    On 10/20/2016 08:54 AM, Josh Berkus wrote:
> On 10/20/2016 06:34 AM, Joshua D. Drake wrote:
>> On 10/19/2016 07:22 PM, Josh Berkus wrote:
>>> On 10/19/2016 06:27 PM, Joshua D. Drake wrote:

>> Hrm, true although that is by far a minority of our users. What if we
>> made it so we disabled the autovacuum guc but made it so you could
>> disable autovacuum per database (ALTER DATABASE SET or something such
>> thing?).
>
> Well, that wouldn't fix the problem; people would just disable it per
> database, even if it was a bad idea.

I doubt this very much. It requires a different level of sophistication.

General users (not you, not me, and certainly not Haas or Lane) don't 
run anything but an application backed to an ORM. They understand a conf 
file but they aren't going to touch anything they consider
"underneath".

Sincerely,

JD

-- 
Command Prompt, Inc.                  http://the.postgres.company/
                         +1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.