From: | Josh Berkus <josh(at)agliodbs(dot)com> |
---|---|
To: | "Joshua D(dot) Drake" <jd(at)commandprompt(dot)com>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Disable autovacuum guc? |
Date: | 2016-10-20 15:54:43 |
Message-ID: | 31c84e7a-0c1f-ea20-8446-a6c42eb73868@agliodbs.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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)
From | Date | Subject | |
---|---|---|---|
Next Message | Joshua D. Drake | 2016-10-20 16:05:00 | Re: Disable autovacuum guc? |
Previous Message | Joshua D. Drake | 2016-10-20 15:53:03 | Re: Remove autovacuum GUC? |