Should we increase the default vacuum_cost_limit?

On Sun, Feb 24, 2019 at 9:42 PM David Rowley
<david.rowley@2ndquadrant.com> wrote:

The current default vacuum_cost_limit of 200 seems to be 15 years old
and was added in f425b605f4e.

Any supporters for raising the default?

I also think that the current default limit is far too conservative.

--
Peter Geoghegan

I support rising the default.

From standpoint of no-clue database admin, it's easier to give more
resources to Postgres and google what process called "autovacuum" does than
to learn why is it being slow on read.

It's also tricky that index only scans depend on working autovacuum, and
autovacuum never comes to those tables. Since PG11 it's safe to call vacuum
on table with indexes, since index is no longer being scanned in its
entirety. I would also propose to include "tuples inserted" into formula
for autovacuum threshold the same way it is done for autoanalyze threshold.
This will fix the situation where you delete 50 rows in 100-gigabyte table
and autovacuum suddenly goes to rewrite and reread hint bits on all of it,
since it never touched it before.

On Mon, Feb 25, 2019 at 8:42 AM David Rowley <david.rowley@2ndquadrant.com>
wrote:

Hi,

I've had to do quite a bit of performance investigation work this year
and it seems that I only too often discover that the same problem is
repeating itself... A vacuum_cost_limit that is still set to the 200
default value along with all 3 auto-vacuum workers being flat chat
trying and failing to keep up with the demand.

I understand we often keep the default config aimed at low-end
servers, but I don't believe we should categorise this option the same
way as we do with shared_buffers and work_mem. What's to say that
having an auto-vacuum that runs too slowly is better than one that
runs too quickly?

I have in mind that performance problems arising from having
auto-vacuum run too quickly might be easier to diagnose and fix than
the ones that arise from it running too slowly. Certainly, the
aftermath cleanup involved with it running too slowly is quite a bit
more tricky to solve.

Ideally, we'd have something smarter than the cost limits we have
today, something that perhaps is adaptive and can make more use of an
idle server than we do now, but that sounds like a pretty large
project to consider having it working this late in the cycle.

In the meantime, should we consider not having vacuum_cost_limit set
so low by default?

I have in mind something in the ballpark of a 5x to 10x increase. It
seems the standard settings only allow for a maximum of ~3.9MB/s dirty
rate and ~7.8MB/s shared buffer miss rate. That seems pretty slow
even for the micro SD card that's in my 4-year-old phone. I think we
should be aiming for setting this to something good for the slightly
better than average case of modern hardware.

The current default vacuum_cost_limit of 200 seems to be 15 years old
and was added in f425b605f4e.

Any supporters for raising the default?

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

--
Darafei Praliaskouski
Support me: http://patreon.com/komzpa

On 2/25/19 1:17 AM, Peter Geoghegan wrote:

On Sun, Feb 24, 2019 at 9:42 PM David Rowley
<david.rowley@2ndquadrant.com> wrote:

The current default vacuum_cost_limit of 200 seems to be 15 years old
and was added in f425b605f4e.

Any supporters for raising the default?

I also think that the current default limit is far too conservative.

I agree entirely. In my experience you are usually much better off if
vacuum finishes quickly. Personally I think our default scale factors
are horrible too, especially when there are tables with large numbers of
rows.

Joe

--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development

On Tue, 26 Feb 2019 at 02:06, Joe Conway <mail@joeconway.com> wrote:

On 2/25/19 1:17 AM, Peter Geoghegan wrote:

On Sun, Feb 24, 2019 at 9:42 PM David Rowley
<david.rowley@2ndquadrant.com> wrote:

The current default vacuum_cost_limit of 200 seems to be 15 years old
and was added in f425b605f4e.

Any supporters for raising the default?

I also think that the current default limit is far too conservative.

I agree entirely. In my experience you are usually much better off if
vacuum finishes quickly. Personally I think our default scale factors
are horrible too, especially when there are tables with large numbers of
rows.

Agreed that the scale factors are not perfect, but I don't think
changing them is as quite a no-brainer as the vacuum_cost_limit, so
the attached patch just does the vacuum_cost_limit.

I decided to do the times by 10 option that I had mentioned.... Ensue
debate about that...

I'll add this to the March commitfest and set the target version as PG12.

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

David Rowley wrote:

On Tue, 26 Feb 2019 at 02:06, Joe Conway <mail@joeconway.com> wrote:

On 2/25/19 1:17 AM, Peter Geoghegan wrote:

On Sun, Feb 24, 2019 at 9:42 PM David Rowley
<david.rowley@2ndquadrant.com> wrote:

The current default vacuum_cost_limit of 200 seems to be 15 years old
and was added in f425b605f4e.

Any supporters for raising the default?

I also think that the current default limit is far too conservative.

I agree entirely. In my experience you are usually much better off if
vacuum finishes quickly. Personally I think our default scale factors
are horrible too, especially when there are tables with large numbers of
rows.

Agreed that the scale factors are not perfect, but I don't think
changing them is as quite a no-brainer as the vacuum_cost_limit, so
the attached patch just does the vacuum_cost_limit.

I decided to do the times by 10 option that I had mentioned.... Ensue
debate about that...

I'll add this to the March commitfest and set the target version as PG12.

I think this is a good move.

It is way easier to recover from an over-eager autovacuum than from
one that didn't manage to finish...

Yours,
Laurenz Albe

On Mon, Feb 25, 2019 at 4:44 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:

David Rowley wrote:

On Tue, 26 Feb 2019 at 02:06, Joe Conway <mail@joeconway.com> wrote:

On 2/25/19 1:17 AM, Peter Geoghegan wrote:

On Sun, Feb 24, 2019 at 9:42 PM David Rowley
<david.rowley@2ndquadrant.com> wrote:

The current default vacuum_cost_limit of 200 seems to be 15 years old
and was added in f425b605f4e.

Any supporters for raising the default?

[...]
I'll add this to the March commitfest and set the target version as PG12.

I think this is a good move.

It is way easier to recover from an over-eager autovacuum than from
one that didn't manage to finish...

+1

On Mon, Feb 25, 2019 at 8:39 AM David Rowley
<david.rowley@2ndquadrant.com> wrote:

I decided to do the times by 10 option that I had mentioned.... Ensue
debate about that...

+1 for raising the default substantially. In my experience, and it
seems others are in a similar place, nobody ever gets into trouble
because the default is too high, but sometimes people get in trouble
because the default is too low. If we raise it enough that a few
people have to reduce it and a few people have to further increase it,
IMHO that would be about right. Not sure exactly what value would
accomplish that goal.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas wrote:

Not sure exactly what value would accomplish that goal.

I think autovacuum_vacuum_cost_limit = 2000 is a good starting point.

Yours,
Laurenz Albe

On Mon, Feb 25, 2019 at 8:48 AM Robert Haas <robertmhaas@gmail.com> wrote:

+1 for raising the default substantially. In my experience, and it
seems others are in a similar place, nobody ever gets into trouble
because the default is too high, but sometimes people get in trouble
because the default is too low.

Does anyone want to make an argument against the idea of raising the
default? They should speak up now.

--
Peter Geoghegan

On 3/5/19 1:14 AM, Peter Geoghegan wrote:

On Mon, Feb 25, 2019 at 8:48 AM Robert Haas <robertmhaas@gmail.com> wrote:

+1 for raising the default substantially. In my experience, and it
seems others are in a similar place, nobody ever gets into trouble
because the default is too high, but sometimes people get in trouble
because the default is too low.

Does anyone want to make an argument against the idea of raising the
default? They should speak up now.

I don't know.

On the one hand I don't feel very strongly about this change, and I have
no intention to block it (because in most cases I do actually increase
the value anyway). I wonder if those with small systems will be happy
about it, though.

But on the other hand it feels a bit weird that we increase this one
value and leave all the other (also very conservative) defaults alone.

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On Tue, Mar 5, 2019 at 7:53 AM Tomas Vondra
<tomas.vondra@2ndquadrant.com> wrote:

But on the other hand it feels a bit weird that we increase this one
value and leave all the other (also very conservative) defaults alone.

Are you talking about vacuum-related defaults or defaults in general?
In 2014, we increased the defaults for work_mem and
maintenance_work_mem by 4x and the default for effective_cache_size by
32x; in 2012, we increased the default for shared_buffers from by 4x.
It's possible some of those parameters should be further increased at
some point, but deciding not to increase any of them until we can
increase all of them is tantamount to giving up on changing anything
at all. I think it's OK to be conservative by default, but we should
increase parameters where we know that the default is likely to be too
conservative for 99% of users. My only question about this change is
whether to go for a lesser multiple (e.g. 4x) rather than the proposed
10x. But I think even if 10x turns out to be too much for a few more
people than we'd like, we're still going to be better off increasing
it and having some people have to turn it back down again than leaving
it the way it is and having users regularly suffer vacuum-starvation.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 2/25/19 8:38 AM, David Rowley wrote:

On Tue, 26 Feb 2019 at 02:06, Joe Conway <mail@joeconway.com> wrote:

On 2/25/19 1:17 AM, Peter Geoghegan wrote:

On Sun, Feb 24, 2019 at 9:42 PM David Rowley
<david.rowley@2ndquadrant.com> wrote:

The current default vacuum_cost_limit of 200 seems to be 15 years old
and was added in f425b605f4e.

Any supporters for raising the default?

I also think that the current default limit is far too conservative.

I agree entirely. In my experience you are usually much better off if
vacuum finishes quickly. Personally I think our default scale factors
are horrible too, especially when there are tables with large numbers of
rows.

Agreed that the scale factors are not perfect, but I don't think
changing them is as quite a no-brainer as the vacuum_cost_limit, so
the attached patch just does the vacuum_cost_limit.

I decided to do the times by 10 option that I had mentioned.... Ensue
debate about that...

I'll add this to the March commitfest and set the target version as PG12.

This patch is tiny, seems perfectly reasonable, and has plenty of
support. I'm going to commit it shortly unless there are last minute
objections.

cheers

andrew

--
Andrew Dunstan https://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On 2019-03-05 17:14:55 -0500, Andrew Dunstan wrote:

This patch is tiny, seems perfectly reasonable, and has plenty of
support. I'm going to commit it shortly unless there are last minute
objections.

+1

Thanks for chipping in on this.

On Wed, 6 Mar 2019 at 01:53, Tomas Vondra <tomas.vondra@2ndquadrant.com> wrote:

But on the other hand it feels a bit weird that we increase this one
value and leave all the other (also very conservative) defaults alone.

Which others did you have in mind? Like work_mem, shared_buffers? If
so, I mentioned in the initial post that I don't see vacuum_cost_limit
as in the same category as those. It's not like PostgreSQL won't
start on a tiny server if vacuum_cost_limit is too high, but you will
have issues with too big a shared_buffers, for example. I think if
we insist that this patch is a review of all the "how big is your
server" GUCs then that's raising the bar significantly and
unnecessarily for what I'm proposing here.

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

On 3/5/19 14:14, Andrew Dunstan wrote:

This patch is tiny, seems perfectly reasonable, and has plenty of
support. I'm going to commit it shortly unless there are last minute
objections.

+1

--
Jeremy Schneider
Database Engineer
Amazon Web Services

On 3/6/19 1:38 PM, Jeremy Schneider wrote:

On 3/5/19 14:14, Andrew Dunstan wrote:

This patch is tiny, seems perfectly reasonable, and has plenty of
support. I'm going to commit it shortly unless there are last minute
objections.

+1

done.

cheers

andrew

--
Andrew Dunstan https://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On 3/6/19 12:10 AM, David Rowley wrote:

Thanks for chipping in on this.

On Wed, 6 Mar 2019 at 01:53, Tomas Vondra <tomas.vondra@2ndquadrant.com> wrote:

But on the other hand it feels a bit weird that we increase this one
value and leave all the other (also very conservative) defaults alone.

Which others did you have in mind? Like work_mem, shared_buffers? If
so, I mentioned in the initial post that I don't see vacuum_cost_limit
as in the same category as those. It's not like PostgreSQL won't
start on a tiny server if vacuum_cost_limit is too high, but you will
have issues with too big a shared_buffers, for example. I think if
we insist that this patch is a review of all the "how big is your
server" GUCs then that's raising the bar significantly and
unnecessarily for what I'm proposing here.

On second thought, I think you're right. It's still true that you need
to bump up various other GUCs on reasonably current hardware, but it's
true vacuum_cost_limit is special enough to increase it separately.

so +1 (I see Andrew already pushed it, but anyway ...)

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On Thu, 7 Mar 2019 at 08:54, Andrew Dunstan
<andrew.dunstan@2ndquadrant.com> wrote:

On 3/6/19 1:38 PM, Jeremy Schneider wrote:

On 3/5/19 14:14, Andrew Dunstan wrote:

This patch is tiny, seems perfectly reasonable, and has plenty of
support. I'm going to commit it shortly unless there are last minute
objections.

+1

done.

Thanks!

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

On Wed, Mar 6, 2019 at 2:54 PM Andrew Dunstan <
andrew.dunstan@2ndquadrant.com> wrote:

On 3/6/19 1:38 PM, Jeremy Schneider wrote:

On 3/5/19 14:14, Andrew Dunstan wrote:

This patch is tiny, seems perfectly reasonable, and has plenty of
support. I'm going to commit it shortly unless there are last minute
objections.

+1

done.

Now that this is done, the default value is only 5x below the hard-coded
maximum of 10,000.

This seems a bit odd, and not very future-proof. Especially since the
hard-coded maximum appears to have no logic to it anyway, at least none
that is documented. Is it just mindless nannyism?

Any reason not to increase by at least a factor of 10, but preferably the
largest value that does not cause computational problems (which I think
would be INT_MAX)?

Cheers,

Jeff