Thread: Disable autovacuum guc?
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.
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)
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
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
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.
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)
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.