Horton

Re: Should we increase the default vacuum_cost_limit?

Tom Lane <tgl@sss.pgh.pa.us>

View thread View on postgr.es
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Andrew Dunstan <andrew.dunstan@2ndquadrant.com>
Cc: David Rowley <david.rowley@2ndquadrant.com>, Jeff Janes <jeff.janes@gmail.com>, Jeremy Schneider <schnjere@amazon.com>, Joe Conway <mail@joeconway.com>, Peter Geoghegan <pg@bowt.ie>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2019-03-09T01:54:11Z
Lists: pgsql-hackers 
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

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