Horton

Thread

Commits

  1. Allow fractional input values for integer GUCs, and improve rounding logic.

  2. Reduce the default value of autovacuum_vacuum_cost_delay to 2ms.

  3. Revert "Increase the default vacuum_cost_limit from 200 to 2000"

  4. Convert [autovacuum_]vacuum_cost_delay into floating-point GUCs.

  5. Include GUC's unit, if it has one, in out-of-range error messages.

  6. Disallow NaN as a value for floating-point GUCs.

  7. Increase the default vacuum_cost_limit from 200 to 2000

  1. Should we increase the default vacuum_cost_limit?

    David Rowley <david.rowley@2ndquadrant.com> — 2019-02-25T05:42:12Z

    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

  2. Re: Should we increase the default vacuum_cost_limit?

    Peter Geoghegan <pg@bowt.ie> — 2019-02-25T06:17:09Z

    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

  3. Re: Should we increase the default vacuum_cost_limit?

    Darafei Komяpa Praliaskouski <me@komzpa.net> — 2019-02-25T09:05:40Z

    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

  4. Re: Should we increase the default vacuum_cost_limit?

    Joe Conway <mail@joeconway.com> — 2019-02-25T13:06:45Z

    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

  5. Re: Should we increase the default vacuum_cost_limit?

    David Rowley <david.rowley@2ndquadrant.com> — 2019-02-25T13:38:59Z

    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

  6. Re: Should we increase the default vacuum_cost_limit?

    Laurenz Albe <laurenz.albe@cybertec.at> — 2019-02-25T15:43:40Z

    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

  7. Re: Should we increase the default vacuum_cost_limit?

    Julien Rouhaud <rjuju123@gmail.com> — 2019-02-25T16:39:29Z

    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

  8. Re: Should we increase the default vacuum_cost_limit?

    Robert Haas <robertmhaas@gmail.com> — 2019-02-25T16:48:16Z

    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

  9. Re: Should we increase the default vacuum_cost_limit?

    Laurenz Albe <laurenz.albe@cybertec.at> — 2019-02-26T12:04:56Z

    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

  10. Re: Should we increase the default vacuum_cost_limit?

    Peter Geoghegan <pg@bowt.ie> — 2019-03-05T00:14:40Z

    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

  11. Re: Should we increase the default vacuum_cost_limit?

    Tomas Vondra <tomas.vondra@2ndquadrant.com> — 2019-03-05T12:53:07Z

    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

  12. Re: Should we increase the default vacuum_cost_limit?

    Robert Haas <robertmhaas@gmail.com> — 2019-03-05T15:29:08Z

    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

  13. Re: Should we increase the default vacuum_cost_limit?

    Andrew Dunstan <andrew.dunstan@2ndquadrant.com> — 2019-03-05T22:14:55Z

    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

  14. Re: Should we increase the default vacuum_cost_limit?

    Andres Freund <andres@anarazel.de> — 2019-03-05T22:19:19Z

    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

  15. Re: Should we increase the default vacuum_cost_limit?

    David Rowley <david.rowley@2ndquadrant.com> — 2019-03-05T23:10:42Z

    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

  16. Re: Should we increase the default vacuum_cost_limit?

    Jeremy Schneider <schnjere@amazon.com> — 2019-03-06T18:38:10Z

    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

  17. Re: Should we increase the default vacuum_cost_limit?

    Andrew Dunstan <andrew.dunstan@2ndquadrant.com> — 2019-03-06T19:54:27Z

    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

  18. Re: Should we increase the default vacuum_cost_limit?

    Tomas Vondra <tomas.vondra@2ndquadrant.com> — 2019-03-06T20:26:49Z

    
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

  19. Re: Should we increase the default vacuum_cost_limit?

    David Rowley <david.rowley@2ndquadrant.com> — 2019-03-06T22:01:19Z

    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

  20. Re: Should we increase the default vacuum_cost_limit?

    Jeff Janes <jeff.janes@gmail.com> — 2019-03-08T15:20:19Z

    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

  21. Re: Should we increase the default vacuum_cost_limit?

    Tom Lane <tgl@sss.pgh.pa.us> — 2019-03-08T18:10:30Z

    Jeff Janes <jeff.janes@gmail.com> writes:
> 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?

Hm.  I think the idea was that rather than setting it to "something very
large", you'd want to just disable the feature via vacuum_cost_delay.
But I agree that the threshold for what is ridiculously large probably
ought to be well more than 5x the default, and maybe it is just mindless
nannyism to have a limit less than what the implementation can handle.

			regards, tom lane

  22. Re: Should we increase the default vacuum_cost_limit?

    David Rowley <david.rowley@2ndquadrant.com> — 2019-03-08T23:47:34Z

    On Sat, 9 Mar 2019 at 07:10, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Jeff Janes <jeff.janes@gmail.com> writes:
> > 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?
>
> Hm.  I think the idea was that rather than setting it to "something very
> large", you'd want to just disable the feature via vacuum_cost_delay.
> But I agree that the threshold for what is ridiculously large probably
> ought to be well more than 5x the default, and maybe it is just mindless
> nannyism to have a limit less than what the implementation can handle.

Yeah, +1 to increasing it.  I imagine that the 10,000 limit would not
allow people to explore the upper limits of a modern PCI-E SSD with
the standard delay time and dirty/miss scores.  Also, it doesn't seem
entirely unreasonable that someone somewhere might also want to
fine-tune the hit/miss/dirty scores so that they're some larger factor
apart from each other the standard scores are. The 10,000 limit does
not allow much wiggle room for that.

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

  23. Re: Should we increase the default vacuum_cost_limit?

    Andrew Dunstan <andrew.dunstan@2ndquadrant.com> — 2019-03-09T01:29:14Z

    On 3/8/19 6:47 PM, David Rowley wrote:
> On Sat, 9 Mar 2019 at 07:10, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Jeff Janes <jeff.janes@gmail.com> writes:
>>> 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?
>> Hm.  I think the idea was that rather than setting it to "something very
>> large", you'd want to just disable the feature via vacuum_cost_delay.
>> But I agree that the threshold for what is ridiculously large probably
>> ought to be well more than 5x the default, and maybe it is just mindless
>> nannyism to have a limit less than what the implementation can handle.
> Yeah, +1 to increasing it.  I imagine that the 10,000 limit would not
> allow people to explore the upper limits of a modern PCI-E SSD with
> the standard delay time and dirty/miss scores.  Also, it doesn't seem
> entirely unreasonable that someone somewhere might also want to
> fine-tune the hit/miss/dirty scores so that they're some larger factor
> apart from each other the standard scores are. The 10,000 limit does
> not allow much wiggle room for that.
>


Increase it to what?


cheers


andrew



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

  24. Re: Should we increase the default vacuum_cost_limit?

    Tom Lane <tgl@sss.pgh.pa.us> — 2019-03-09T01:54:11Z

    Andrew Dunstan <andrew.dunstan@2ndquadrant.com> writes:
> Increase it to what?

Jeff's opinion that it could be INT_MAX without causing trouble is
a bit over-optimistic, see vacuum_delay_point():

    if (VacuumCostActive && !InterruptPending &&
        VacuumCostBalance >= VacuumCostLimit)
    {
        int            msec;

        msec = VacuumCostDelay * VacuumCostBalance / VacuumCostLimit;

In the first place, if VacuumCostLimit is too close to INT_MAX,
it'd be possible for VacuumCostBalance (also an int) to overflow
between visits to vacuum_delay_point, wrapping around to negative
and thus missing the need to nap altogether.

In the second place, since large VacuumCostLimit implies large
VacuumCostBalance when we do get through this if-check, there's
a hazard of integer overflow in the VacuumCostDelay * VacuumCostBalance
multiplication.  The final value of the msec calculation should be
easily within integer range, since VacuumCostDelay is constrained to
not be very large, but that's no help if we have intermediate overflow.

Possibly we could forestall both of those problems by changing
VacuumCostBalance to double, but that would make the cost
bookkeeping noticeably more expensive than it used to be.
I think it'd be better to keep VacuumCostBalance as int,
which would then mean we'd better limit VacuumCostLimit to no
more than say INT_MAX/2 --- call it 1 billion for the sake of
a round number.

That'd still leave us at risk of an integer overflow in the
msec-to-sleep calculation, but that calculation could be changed
to double at little price, since once we get here we're going
to sleep awhile anyway.

BTW, don't forget autovacuum_cost_limit should have the same maximum.

			regards, tom lane

  25. Re: Should we increase the default vacuum_cost_limit?

    Tom Lane <tgl@sss.pgh.pa.us> — 2019-03-09T03:11:26Z

    I wrote:
> [ worries about overflow with VacuumCostLimit approaching INT_MAX ]

Actually, now that I think a bit harder, that disquisition was silly.
In fact, I'm inclined to argue that the already-committed patch
is taking the wrong approach, and we should revert it in favor of a
different idea.

The reason is this: what we want to do is throttle VACUUM's I/O demand,
and by "throttle" I mean "gradually reduce".  There is nothing gradual
about issuing a few million I/Os and then sleeping for many milliseconds;
that'll just produce spikes and valleys in the I/O demand.  Ideally,
what we'd have it do is sleep for a very short interval after each I/O.
But that's not too practical, both for code-structure reasons and because
most platforms don't give us a way to so finely control the length of a
sleep.  Hence the design of sleeping for awhile after every so many I/Os.

However, the current settings are predicated on the assumption that
you can't get the kernel to give you a sleep of less than circa 10ms.
That assumption is way outdated, I believe; poking around on systems
I have here, the minimum delay time using pg_usleep(1) seems to be
generally less than 100us, and frequently less than 10us, on anything
released in the last decade.

I propose therefore that instead of increasing vacuum_cost_limit,
what we ought to be doing is reducing vacuum_cost_delay by a similar
factor.  And, to provide some daylight for people to reduce it even
more, we ought to arrange for it to be specifiable in microseconds
not milliseconds.  There's no GUC_UNIT_US right now, but it's time.
(Perhaps we should also look into using other delay APIs, such as
nanosleep(2), where available.)

I don't have any particular objection to kicking up the maximum
value of vacuum_cost_limit by 10X or so, if anyone's hot to do that.
But that's not where we ought to be focusing our concern.  And there
really is a good reason, not just nannyism, not to make that
setting huge --- it's just the wrong thing to do, as compared to
reducing vacuum_cost_delay.

			regards, tom lane

  26. Re: Should we increase the default vacuum_cost_limit?

    David Rowley <david.rowley@2ndquadrant.com> — 2019-03-09T09:28:12Z

    On Sat, 9 Mar 2019 at 16:11, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> I propose therefore that instead of increasing vacuum_cost_limit,
> what we ought to be doing is reducing vacuum_cost_delay by a similar
> factor.  And, to provide some daylight for people to reduce it even
> more, we ought to arrange for it to be specifiable in microseconds
> not milliseconds.  There's no GUC_UNIT_US right now, but it's time.
> (Perhaps we should also look into using other delay APIs, such as
> nanosleep(2), where available.)

It does seem like a genuine concern that there might be too much all
or nothing. It's no good being on a highspeed train if it stops at
every platform.

I agree that vacuum_cost_delay might not be granular enough, however.
If we're going to change the vacuum_cost_delay into microseconds, then
I'm a little concerned that it'll silently break existing code that
sets it.  Scripts that do manual off-peak vacuums are pretty common
out in the wild.

In an ideal world we'd just redesign the vacuum throttling to have
MB/s for hit/read/dirty, and possible also WAL write rate.  I'm not
sure exactly how they'd cooperate together, but we could likely
minimise gettimeofday() calls by sampling the time it took to process
N pages, and if N pages didn't take the time we wanted them to take we
could set N = Min(N * ($target_gettimeofday_sample_rate / $timetaken),
1);  e.g if N was 2000 and it just took us 1 second to do 2000 pages,
but we want to sleep every millisecond, then just do N *= (0.001 / 1),
so the next run we only do 2 pages before checking how long we should
sleep for. If we happened to process those 2 pages in 0.5
milliseconds, then N would become 4, etc.

We'd just need to hard code the $target_gettimeofday_sample_rate.
Probably 1 millisecond would be about right and we'd need to just
guess the first value of N, but if we guess a low value, it'll be
quick to correct itself after the first batch of pages.

If anyone thinks that idea has any potential, then maybe it's better
to leave the new vacuum_cost_limit default in place and consider
redesigning this for PG13... as such a change is too late for PG12.

It may also be possible to make this a vacuum rate limit in %. Say 10%
would just sleep for 10x as long is it took to process the last set of
pages.   The problem with this is that if the server was under heavy
load then auto-vacuum might crawl along, but that might be the exact
opposite of what's required as it might be crawling due to inadequate
vacuuming.

> I don't have any particular objection to kicking up the maximum
> value of vacuum_cost_limit by 10X or so, if anyone's hot to do that.
> But that's not where we ought to be focusing our concern.  And there
> really is a good reason, not just nannyism, not to make that
> setting huge --- it's just the wrong thing to do, as compared to
> reducing vacuum_cost_delay.

My vote is to 10x the maximum for vacuum_cost_limit and consider
changing how it all works in PG13.  If nothing happens before this
time next year then we can consider making vacuum_cost_delay a
microseconds GUC.


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

  27. Re: Should we increase the default vacuum_cost_limit?

    Andrew Dunstan <andrew.dunstan@2ndquadrant.com> — 2019-03-09T13:28:22Z

    On 3/9/19 4:28 AM, David Rowley wrote:
> On Sat, 9 Mar 2019 at 16:11, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> I propose therefore that instead of increasing vacuum_cost_limit,
>> what we ought to be doing is reducing vacuum_cost_delay by a similar
>> factor.  And, to provide some daylight for people to reduce it even
>> more, we ought to arrange for it to be specifiable in microseconds
>> not milliseconds.  There's no GUC_UNIT_US right now, but it's time.
>> (Perhaps we should also look into using other delay APIs, such as
>> nanosleep(2), where available.)
> It does seem like a genuine concern that there might be too much all
> or nothing. It's no good being on a highspeed train if it stops at
> every platform.
>
> I agree that vacuum_cost_delay might not be granular enough, however.
> If we're going to change the vacuum_cost_delay into microseconds, then
> I'm a little concerned that it'll silently break existing code that
> sets it.  Scripts that do manual off-peak vacuums are pretty common
> out in the wild.


Maybe we could leave the default units as msec but store it and allow
specifying as usec. Not sure how well the GUC mechanism would cope with
that.


[other good ideas]


>> I don't have any particular objection to kicking up the maximum
>> value of vacuum_cost_limit by 10X or so, if anyone's hot to do that.
>> But that's not where we ought to be focusing our concern.  And there
>> really is a good reason, not just nannyism, not to make that
>> setting huge --- it's just the wrong thing to do, as compared to
>> reducing vacuum_cost_delay.
> My vote is to 10x the maximum for vacuum_cost_limit and consider
> changing how it all works in PG13.  If nothing happens before this
> time next year then we can consider making vacuum_cost_delay a
> microseconds GUC.
>

+1.


cheers


andrew

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

  28. Re: Should we increase the default vacuum_cost_limit?

    Tom Lane <tgl@sss.pgh.pa.us> — 2019-03-09T16:31:36Z

    David Rowley <david.rowley@2ndquadrant.com> writes:
> I agree that vacuum_cost_delay might not be granular enough, however.
> If we're going to change the vacuum_cost_delay into microseconds, then
> I'm a little concerned that it'll silently break existing code that
> sets it.  Scripts that do manual off-peak vacuums are pretty common
> out in the wild.

True.  Perhaps we could keep the units as ms but make it a float?
Not sure if the "units" logic can cope though.

> My vote is to 10x the maximum for vacuum_cost_limit and consider
> changing how it all works in PG13.  If nothing happens before this
> time next year then we can consider making vacuum_cost_delay a
> microseconds GUC.

I'm not really happy with the idea of changing the defaults in this area
and then changing them again next year.  That's going to lead to a lot
of confusion, and a mess for people who may have changed (some) of
the settings manually.

			regards, tom lane

  29. Re: Should we increase the default vacuum_cost_limit?

    Tom Lane <tgl@sss.pgh.pa.us> — 2019-03-09T17:55:54Z

    Andrew Dunstan <andrew.dunstan@2ndquadrant.com> writes:
> On 3/9/19 4:28 AM, David Rowley wrote:
>> I agree that vacuum_cost_delay might not be granular enough, however.
>> If we're going to change the vacuum_cost_delay into microseconds, then
>> I'm a little concerned that it'll silently break existing code that
>> sets it.  Scripts that do manual off-peak vacuums are pretty common
>> out in the wild.

> Maybe we could leave the default units as msec but store it and allow
> specifying as usec. Not sure how well the GUC mechanism would cope with
> that.

I took a quick look at that and I'm afraid it'd be a mess.  GUC doesn't
really distinguish between a variable's storage unit, its default input
unit, or its default output unit (as seen in e.g. pg_settings).  Perhaps
we could split those into two or three distinct concepts, but it seems
complicated and bug-prone.  Also I think we'd still be forced into
making obviously-incompatible changes in what pg_settings shows for
this variable, since what it shows right now is integer ms.  That last
isn't a deal-breaker perhaps, but 100% compatibility isn't going to
happen this way.

The idea of converting vacuum_cost_delay into a float variable, while
keeping its native unit as ms, seems probably more feasible from a
compatibility standpoint.  There are two sub-possibilities:

1. Just do that and lose units support for the variable.  I don't
think this is totally unreasonable, because up to now ms is the
*only* workable unit for it:

regression=# set vacuum_cost_delay = '1s';
ERROR:  1000 is outside the valid range for parameter "vacuum_cost_delay" (0 .. 100)

Still, it'd mean that anyone who'd been explicitly setting it with
an "ms" qualifier would have to change their postgresql.conf entry.

2. Add support for units for float variables, too.  I don't think
this'd be a huge amount of work, and we'd surely have other uses
for it in the long run.

I'm inclined to go look into #2.  Anybody think this is a bad idea?

			regards, tom lane

  30. Re: Should we increase the default vacuum_cost_limit?

    Andrew Dunstan <andrew.dunstan@2ndquadrant.com> — 2019-03-09T18:58:20Z

    On 3/9/19 12:55 PM, Tom Lane wrote:
> Andrew Dunstan <andrew.dunstan@2ndquadrant.com> writes:
>> On 3/9/19 4:28 AM, David Rowley wrote:
>>> I agree that vacuum_cost_delay might not be granular enough, however.
>>> If we're going to change the vacuum_cost_delay into microseconds, then
>>> I'm a little concerned that it'll silently break existing code that
>>> sets it.  Scripts that do manual off-peak vacuums are pretty common
>>> out in the wild.
>> Maybe we could leave the default units as msec but store it and allow
>> specifying as usec. Not sure how well the GUC mechanism would cope with
>> that.
> I took a quick look at that and I'm afraid it'd be a mess.  GUC doesn't
> really distinguish between a variable's storage unit, its default input
> unit, or its default output unit (as seen in e.g. pg_settings).  Perhaps
> we could split those into two or three distinct concepts, but it seems
> complicated and bug-prone.  Also I think we'd still be forced into
> making obviously-incompatible changes in what pg_settings shows for
> this variable, since what it shows right now is integer ms.  That last
> isn't a deal-breaker perhaps, but 100% compatibility isn't going to
> happen this way.
>
> The idea of converting vacuum_cost_delay into a float variable, while
> keeping its native unit as ms, seems probably more feasible from a
> compatibility standpoint.  There are two sub-possibilities:
>
> 1. Just do that and lose units support for the variable.  I don't
> think this is totally unreasonable, because up to now ms is the
> *only* workable unit for it:
>
> regression=# set vacuum_cost_delay = '1s';
> ERROR:  1000 is outside the valid range for parameter "vacuum_cost_delay" (0 .. 100)
>
> Still, it'd mean that anyone who'd been explicitly setting it with
> an "ms" qualifier would have to change their postgresql.conf entry.
>
> 2. Add support for units for float variables, too.  I don't think
> this'd be a huge amount of work, and we'd surely have other uses
> for it in the long run.
>
> I'm inclined to go look into #2.  Anybody think this is a bad idea?
>
> 	


Sounds good to me, seems much more likely to be future-proof.


cheers


andrew


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

  31. Re: Should we increase the default vacuum_cost_limit?

    Julien Rouhaud <rjuju123@gmail.com> — 2019-03-09T19:12:26Z

    On Sat, Mar 9, 2019 at 7:58 PM Andrew Dunstan
<andrew.dunstan@2ndquadrant.com> wrote:
>
> On 3/9/19 12:55 PM, Tom Lane wrote:
> >> Maybe we could leave the default units as msec but store it and allow
> >> specifying as usec. Not sure how well the GUC mechanism would cope with
> >> that.
> > I took a quick look at that and I'm afraid it'd be a mess.  GUC doesn't
> > really distinguish between a variable's storage unit, its default input
> > unit, or its default output unit (as seen in e.g. pg_settings).  Perhaps
> > we could split those into two or three distinct concepts, but it seems
> > complicated and bug-prone.  Also I think we'd still be forced into
> > making obviously-incompatible changes in what pg_settings shows for
> > this variable, since what it shows right now is integer ms.  That last
> > isn't a deal-breaker perhaps, but 100% compatibility isn't going to
> > happen this way.
> >
> > The idea of converting vacuum_cost_delay into a float variable, while
> > keeping its native unit as ms, seems probably more feasible from a
> > compatibility standpoint.  There are two sub-possibilities:
> >
> > 1. Just do that and lose units support for the variable.  I don't
> > think this is totally unreasonable, because up to now ms is the
> > *only* workable unit for it:
> >
> > regression=# set vacuum_cost_delay = '1s';
> > ERROR:  1000 is outside the valid range for parameter "vacuum_cost_delay" (0 .. 100)
> >
> > Still, it'd mean that anyone who'd been explicitly setting it with
> > an "ms" qualifier would have to change their postgresql.conf entry.
> >
> > 2. Add support for units for float variables, too.  I don't think
> > this'd be a huge amount of work, and we'd surely have other uses
> > for it in the long run.
> >
> > I'm inclined to go look into #2.  Anybody think this is a bad idea?
>
> Sounds good to me, seems much more likely to be future-proof.

Agreed.

  32. Re: Should we increase the default vacuum_cost_limit?

    Gavin Flower <gavinflower@archidevsys.co.nz> — 2019-03-09T20:36:59Z

    On 10/03/2019 06:55, Tom Lane wrote:
> Andrew Dunstan <andrew.dunstan@2ndquadrant.com> writes:
>> On 3/9/19 4:28 AM, David Rowley wrote:
>>> I agree that vacuum_cost_delay might not be granular enough, however.
>>> If we're going to change the vacuum_cost_delay into microseconds, then
>>> I'm a little concerned that it'll silently break existing code that
>>> sets it.  Scripts that do manual off-peak vacuums are pretty common
>>> out in the wild.
>> Maybe we could leave the default units as msec but store it and allow
>> specifying as usec. Not sure how well the GUC mechanism would cope with
>> that.
> I took a quick look at that and I'm afraid it'd be a mess.  GUC doesn't
> really distinguish between a variable's storage unit, its default input
> unit, or its default output unit (as seen in e.g. pg_settings).  Perhaps
> we could split those into two or three distinct concepts, but it seems
> complicated and bug-prone.  Also I think we'd still be forced into
> making obviously-incompatible changes in what pg_settings shows for
> this variable, since what it shows right now is integer ms.  That last
> isn't a deal-breaker perhaps, but 100% compatibility isn't going to
> happen this way.
>
> The idea of converting vacuum_cost_delay into a float variable, while
> keeping its native unit as ms, seems probably more feasible from a
> compatibility standpoint.  There are two sub-possibilities:
>
> 1. Just do that and lose units support for the variable.  I don't
> think this is totally unreasonable, because up to now ms is the
> *only* workable unit for it:
>
> regression=# set vacuum_cost_delay = '1s';
> ERROR:  1000 is outside the valid range for parameter "vacuum_cost_delay" (0 .. 100)
>
> Still, it'd mean that anyone who'd been explicitly setting it with
> an "ms" qualifier would have to change their postgresql.conf entry.
>
> 2. Add support for units for float variables, too.  I don't think
> this'd be a huge amount of work, and we'd surely have other uses
> for it in the long run.
>
> I'm inclined to go look into #2.  Anybody think this is a bad idea?
>
> 			regards, tom lane
>
Hope about  keeping the default unit of ms, but converting it to a 
'double' for input, but storing it as int (or long?) number of 
nanoseconds.  Gives finer grain of control withouthaving to specify a 
unit, while still allowing calculations to be fast?


Cheers,
Gavin

  33. Re: Should we increase the default vacuum_cost_limit?

    Tom Lane <tgl@sss.pgh.pa.us> — 2019-03-09T21:04:39Z

    Julien Rouhaud <rjuju123@gmail.com> writes:
> On Sat, Mar 9, 2019 at 7:58 PM Andrew Dunstan
> <andrew.dunstan@2ndquadrant.com> wrote:
>> On 3/9/19 12:55 PM, Tom Lane wrote:
>>> The idea of converting vacuum_cost_delay into a float variable, while
>>> keeping its native unit as ms, seems probably more feasible from a
>>> compatibility standpoint.  There are two sub-possibilities:
>>> ...
>>> 2. Add support for units for float variables, too.  I don't think
>>> this'd be a huge amount of work, and we'd surely have other uses
>>> for it in the long run.
>>> ...
>>> I'm inclined to go look into #2.  Anybody think this is a bad idea?

>> Sounds good to me, seems much more likely to be future-proof.

> Agreed.

I tried this, and it seems to work pretty well.  The first of the two
attached patches just teaches guc.c to support units for float values,
incidentally allowing "us" as an input unit for time-based GUCs.
The second converts [autovacuum_]cost_delay to float GUCs, and changes
the default value for autovacuum_cost_delay from 20ms to 2ms.
We'd want to revert the previous patch that changed the default value
of vacuum_cost_limit, else this means a 100x not 10x change in the
default autovac speed ... but I've not included that in this patch.

Some notes:

1. I hadn't quite absorbed until doing this that we'd need a catversion
bump because of format change in StdRdOptions.  Since this isn't proposed
for back-patching, that doesn't seem problematic.

2. It's always bugged me that we don't allow fractional unit
specifications, say "0.1GB", even for GUCs that are integers underneath.
That would be a simple additional change on top of this, but I didn't
do it here.

3. I noticed that parse_real doesn't reject infinity or NaN values
for float GUCs.  This seems like a bug, maybe even a back-patchable one;
I doubt the planner will react sanely to SET seq_page_cost TO 'NaN'
for instance.  I didn't do anything about that here either.

4. I've not done anything here about increasing the max value of
[autovacuum_]vacuum_cost_limit.  I have no objection to kicking that
up 10x or so if somebody wants to do the work, but I'm not sure it's
very useful given this patch.

Comments?

			regards, tom lane

  34. Re: Should we increase the default vacuum_cost_limit?

    Tom Lane <tgl@sss.pgh.pa.us> — 2019-03-09T21:06:33Z

    Gavin Flower <GavinFlower@archidevsys.co.nz> writes:
> Hope about  keeping the default unit of ms, but converting it to a 
> 'double' for input, but storing it as int (or long?) number of 
> nanoseconds.  Gives finer grain of control withouthaving to specify a 
> unit, while still allowing calculations to be fast?

Don't really see the point.  The only places where we do any calculations
with the value are where we're about to sleep, so shaving a few nanosec
doesn't seem very interesting.

			regards, tom lane

  35. Re: Should we increase the default vacuum_cost_limit?

    Tom Lane <tgl@sss.pgh.pa.us> — 2019-03-09T22:14:25Z

    BTW ... I noticed while fooling with this that GUC's out-of-range
messages can be confusing:

regression=# set vacuum_cost_delay = '1s';
ERROR:  1000 is outside the valid range for parameter "vacuum_cost_delay" (0 .. 100)

One's immediate reaction to that is "I put in 1, not 1000".  I think
it'd be much clearer if we included the unit we'd converted to, thus:

ERROR:  1000 ms is outside the valid range for parameter "vacuum_cost_delay" (0 .. 100)

(Notice that this also implicitly tells what units the range limits
are being quoted in.  We could repeat the unit name in that part,
viz "(0 .. 100 ms)", but it seems unnecessary.)

A small problem with this idea is that GUC_UNIT_[X]BLOCK variables don't
really have a natural unit name.  If we follow the lead of pg_settings,
such errors would look something like

ERROR:  1000 8kB is outside the valid range for ...

I can't think of a better idea, though, and it'd still be clearer than
what happens now.

Barring objections I'll go make this happen.

			regards, tom lane

  36. Re: Should we increase the default vacuum_cost_limit?

    Julien Rouhaud <rjuju123@gmail.com> — 2019-03-10T13:10:43Z

    On Sat, Mar 9, 2019 at 10:04 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> I tried this, and it seems to work pretty well.  The first of the two
> attached patches just teaches guc.c to support units for float values,
> incidentally allowing "us" as an input unit for time-based GUCs.

Why not allowing third party extensions to declare a GUC stored in us?
 We need a backward-compatible format for vacuum setting, but I don't
see a good reason to force external extensions to do the same, and it
wouldn't require much extra work.

> The second converts [autovacuum_]cost_delay to float GUCs, and changes
> the default value for autovacuum_cost_delay from 20ms to 2ms.
> We'd want to revert the previous patch that changed the default value
> of vacuum_cost_limit, else this means a 100x not 10x change in the
> default autovac speed ... but I've not included that in this patch.

Otherwise everything looks good to me.  BTW the patches didn't apply
cleanly with git-apply, but patch -p1 didn't complain.

> 2. It's always bugged me that we don't allow fractional unit
> specifications, say "0.1GB", even for GUCs that are integers underneath.
> That would be a simple additional change on top of this, but I didn't
> do it here.

It annoyed me multiple times, so +1 for making that happen.

  37. Re: Should we increase the default vacuum_cost_limit?

    Julien Rouhaud <rjuju123@gmail.com> — 2019-03-10T13:18:26Z

    On Sat, Mar 9, 2019 at 11:14 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> BTW ... I noticed while fooling with this that GUC's out-of-range
> messages can be confusing:
>
> regression=# set vacuum_cost_delay = '1s';
> ERROR:  1000 is outside the valid range for parameter "vacuum_cost_delay" (0 .. 100)
>
> One's immediate reaction to that is "I put in 1, not 1000".  I think
> it'd be much clearer if we included the unit we'd converted to, thus:
>
> ERROR:  1000 ms is outside the valid range for parameter "vacuum_cost_delay" (0 .. 100)
>
> (Notice that this also implicitly tells what units the range limits
> are being quoted in.

I like it!

> A small problem with this idea is that GUC_UNIT_[X]BLOCK variables don't
> really have a natural unit name.  If we follow the lead of pg_settings,
> such errors would look something like
>
> ERROR:  1000 8kB is outside the valid range for ...
>
> I can't think of a better idea, though, and it'd still be clearer than
> what happens now.
>
> Barring objections I'll go make this happen.

No objection here.

  38. Re: Should we increase the default vacuum_cost_limit?

    Tom Lane <tgl@sss.pgh.pa.us> — 2019-03-10T15:47:17Z

    Julien Rouhaud <rjuju123@gmail.com> writes:
> On Sat, Mar 9, 2019 at 10:04 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> I tried this, and it seems to work pretty well.  The first of the two
>> attached patches just teaches guc.c to support units for float values,
>> incidentally allowing "us" as an input unit for time-based GUCs.

> Why not allowing third party extensions to declare a GUC stored in us?

I think that adding a new base unit type (GUC_UNIT_US) is possible but
I'm disinclined to do it on the basis of zero evidence that it's needed.
Only three of the five already-known time units are allowed to be base
units (ms, s, min are but d and h aren't) so it's not like there's no
precedent for excluding this one.  Anyway, such a patch would be mostly
orthogonal to what I've done here, so it should be considered on its
own merits.

(BTW, if we're expecting to have GUCs that are meant to measure only
very short time intervals, maybe it'd be more forward-looking for
their base unit to be NS not US.)

>> 2. It's always bugged me that we don't allow fractional unit
>> specifications, say "0.1GB", even for GUCs that are integers underneath.
>> That would be a simple additional change on top of this, but I didn't
>> do it here.

> It annoyed me multiple times, so +1 for making that happen.

OK, will do.

			regards, tom lane

  39. Re: Should we increase the default vacuum_cost_limit?

    Julien Rouhaud <rjuju123@gmail.com> — 2019-03-10T15:56:34Z

    On Sun, Mar 10, 2019 at 4:47 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Julien Rouhaud <rjuju123@gmail.com> writes:
> > On Sat, Mar 9, 2019 at 10:04 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >> I tried this, and it seems to work pretty well.  The first of the two
> >> attached patches just teaches guc.c to support units for float values,
> >> incidentally allowing "us" as an input unit for time-based GUCs.
>
> > Why not allowing third party extensions to declare a GUC stored in us?
>
> I think that adding a new base unit type (GUC_UNIT_US) is possible but
> I'm disinclined to do it on the basis of zero evidence that it's needed.
> Only three of the five already-known time units are allowed to be base
> units (ms, s, min are but d and h aren't) so it's not like there's no
> precedent for excluding this one.  Anyway, such a patch would be mostly
> orthogonal to what I've done here, so it should be considered on its
> own merits.
> (BTW, if we're expecting to have GUCs that are meant to measure only
> very short time intervals, maybe it'd be more forward-looking for
> their base unit to be NS not US.)

That's fair.

> >> 2. It's always bugged me that we don't allow fractional unit
> >> specifications, say "0.1GB", even for GUCs that are integers underneath.
> >> That would be a simple additional change on top of this, but I didn't
> >> do it here.
>
> > It annoyed me multiple times, so +1 for making that happen.
>
> OK, will do.

Thanks!

  40. Re: Should we increase the default vacuum_cost_limit?

    Tom Lane <tgl@sss.pgh.pa.us> — 2019-03-10T20:58:39Z

    Julien Rouhaud <rjuju123@gmail.com> writes:
> On Sat, Mar 9, 2019 at 10:04 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> 2. It's always bugged me that we don't allow fractional unit
>> specifications, say "0.1GB", even for GUCs that are integers underneath.
>> That would be a simple additional change on top of this, but I didn't
>> do it here.

> It annoyed me multiple times, so +1 for making that happen.

The first patch below does that, but I noticed that if we just do it
without any subtlety, you get results like this:

regression=# set work_mem = '30.1GB';
SET
regression=# show work_mem;
  work_mem  
------------
 31562138kB
(1 row)

The second patch is a delta that rounds off to the next smaller unit
if there is one, producing a less noisy result:

regression=# set work_mem = '30.1GB';
SET
regression=# show work_mem;
 work_mem 
----------
 30822MB
(1 row)

I'm not sure if that's a good idea or just overthinking the problem.
Thoughts?

			regards, tom lane

  41. Re: Should we increase the default vacuum_cost_limit?

    David Rowley <david.rowley@2ndquadrant.com> — 2019-03-11T09:03:11Z

    On Mon, 11 Mar 2019 at 09:58, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> The second patch is a delta that rounds off to the next smaller unit
> if there is one, producing a less noisy result:
>
> regression=# set work_mem = '30.1GB';
> SET
> regression=# show work_mem;
>  work_mem
> ----------
>  30822MB
> (1 row)
>
> I'm not sure if that's a good idea or just overthinking the problem.
> Thoughts?

I don't think you're over thinking it.  I often have to look at such
settings and I'm probably not unique in when I glance at 30822MB I can
see that's roughly 30GB, whereas when I look at 31562138kB, I'm either
counting digits or reaching for a calculator.  This is going to reduce
the time it takes for a human to process the pg_settings output, so I
think it's a good idea.

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

  42. Re: Should we increase the default vacuum_cost_limit?

    Julien Rouhaud <rjuju123@gmail.com> — 2019-03-11T12:57:21Z

    On Mon, Mar 11, 2019 at 10:03 AM David Rowley
<david.rowley@2ndquadrant.com> wrote:
>
> On Mon, 11 Mar 2019 at 09:58, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > The second patch is a delta that rounds off to the next smaller unit
> > if there is one, producing a less noisy result:
> >
> > regression=# set work_mem = '30.1GB';
> > SET
> > regression=# show work_mem;
> >  work_mem
> > ----------
> >  30822MB
> > (1 row)
> >
> > I'm not sure if that's a good idea or just overthinking the problem.
> > Thoughts?
>
> I don't think you're over thinking it.  I often have to look at such
> settings and I'm probably not unique in when I glance at 30822MB I can
> see that's roughly 30GB, whereas when I look at 31562138kB, I'm either
> counting digits or reaching for a calculator.  This is going to reduce
> the time it takes for a human to process the pg_settings output, so I
> think it's a good idea.

Definitely, rounding up will spare people from wasting time to check
what's the actual value.

  43. Re: [Suspect SPAM] Re: Should we increase the default vacuum_cost_limit?

    Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp> — 2019-03-12T03:31:28Z

    At Mon, 11 Mar 2019 13:57:21 +0100, Julien Rouhaud <rjuju123@gmail.com> wrote in <CAOBaU_a2tLyonOMJ62=SiDmo84Xo1fy81YA8K=B+=OtTc3sYSQ@mail.gmail.com>
> On Mon, Mar 11, 2019 at 10:03 AM David Rowley
> <david.rowley@2ndquadrant.com> wrote:
> >
> > On Mon, 11 Mar 2019 at 09:58, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > > The second patch is a delta that rounds off to the next smaller unit
> > > if there is one, producing a less noisy result:
> > >
> > > regression=# set work_mem = '30.1GB';
> > > SET
> > > regression=# show work_mem;
> > >  work_mem
> > > ----------
> > >  30822MB
> > > (1 row)
> > >
> > > I'm not sure if that's a good idea or just overthinking the problem.
> > > Thoughts?
> >
> > I don't think you're over thinking it.  I often have to look at such
> > settings and I'm probably not unique in when I glance at 30822MB I can
> > see that's roughly 30GB, whereas when I look at 31562138kB, I'm either
> > counting digits or reaching for a calculator.  This is going to reduce
> > the time it takes for a human to process the pg_settings output, so I
> > think it's a good idea.
> 
> Definitely, rounding up will spare people from wasting time to check
> what's the actual value.

+1. I don't think it overthinking, too.

Anyone who specifies memory size in GB won't care under-MB
fraction. I don't think '0.01GB' is a sane setting but it being
10MB doesn't matter.  However, I don't think that '0.1d' becoming
'2h' is reasonable. "10 times per day" is "rounded" to "12 times
per day" by that.

Is it worth showing values with at most two or three fraction
digits instead of rounding the value on setting? In the attached
PoC patch - instead of the 'roundoff-fractions-harder' patch -
shows values in the shortest exact representation.

work_mem:
   31562138  => '30.1 GB'
   31562137  => '31562137 kB'
   '0.1GB'   => '0.1 GB'
   '0.01GB'  => '0.01 GB'
   '0.001GB' => '1049 kB'

lock_timeout:
   '0.1h'    => '6 min'
   '90 min'  => '90 min'
   '120 min' => '2 h'
   '0.1 d'   => '0.1 d'

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center

  44. Re: [Suspect SPAM] Re: Should we increase the default vacuum_cost_limit?

    Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp> — 2019-03-12T03:45:59Z

    Sorry, I sent a wrong patch. The attached is the right one.

At Mon, 11 Mar 2019 13:57:21 +0100, Julien Rouhaud <rjuju123@gmail.com> wrote in <CAOBaU_a2tLyonOMJ62=SiDmo84Xo1fy81YA8K=B+=OtTc3sYSQ@mail.gmail.com>
> On Mon, Mar 11, 2019 at 10:03 AM David Rowley
> <david.rowley@2ndquadrant.com> wrote:
> >
> > On Mon, 11 Mar 2019 at 09:58, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > > The second patch is a delta that rounds off to the next smaller unit
> > > if there is one, producing a less noisy result:
> > >
> > > regression=# set work_mem = '30.1GB';
> > > SET
> > > regression=# show work_mem;
> > >  work_mem
> > > ----------
> > >  30822MB
> > > (1 row)
> > >
> > > I'm not sure if that's a good idea or just overthinking the problem.
> > > Thoughts?
> >
> > I don't think you're over thinking it.  I often have to look at such
> > settings and I'm probably not unique in when I glance at 30822MB I can
> > see that's roughly 30GB, whereas when I look at 31562138kB, I'm either
> > counting digits or reaching for a calculator.  This is going to reduce
> > the time it takes for a human to process the pg_settings output, so I
> > think it's a good idea.
> 
> Definitely, rounding up will spare people from wasting time to check
> what's the actual value.

+1. I don't think it overthinking, too.

Anyone who specifies memory size in GB won't care under-MB
fraction. I don't think '0.01GB' is a sane setting but it being
10MB doesn't matter.  However, I don't think that '0.1d' becoming
'2h' is reasonable. "10 times per day" is "rounded" to "12 times
per day" by that.

Is it worth showing values with at most two or three fraction
digits instead of rounding the value on setting? In the attached
PoC patch - instead of the 'roundoff-fractions-harder' patch -
shows values in the shortest exact representation.

work_mem:
   31562138  => '30.1 GB'
   31562137  => '31562137 kB'
   '0.1GB'   => '0.1 GB'
   '0.01GB'  => '0.01 GB'
   '0.001GB' => '1049 kB'

lock_timeout:
   '0.1h'    => '6 min'
   '90 min'  => '90 min'
   '120 min' => '2 h'
   '0.1 d'   => '0.1 d'

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center