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>
Subject: Re: Should we increase the default vacuum_cost_limit?
In-reply-to: <26656.1552096451@sss.pgh.pa.us>
References: 
 <CAKJS1f_YbXC2qTMPyCbmsPiKvZYwpuQNQMohiRXLj1r=8_rYvw@mail.gmail.com>
 <CAH2-Wz=WQ9ju6sjPF7RcW0g6NPXAenZeC5qVme6uj+eMRw5q2g@mail.gmail.com>
 <241b80ab-7af0-c606-7b2c-b0a5d6be696a@joeconway.com>
 <CAKJS1f_5xofbg0K16MkhQPeCXV6oGCuD2SVcV76OW7ydFDHDkw@mail.gmail.com>
 <df548503-d261-d8c4-97d0-61be6617df41@2ndQuadrant.com>
 <4a068b09-5a6f-e975-07c9-2333fcef0a8a@amazon.com>
 <30411a5b-ef11-71ef-e9ee-4d26ef3fd0c0@2ndQuadrant.com>
 <CAMkU=1zM7sK9Xo2jjwCLj-t+GHR5nQJnPyFUp+puBiFpOQ7aBA@mail.gmail.com>
 <21034.1552068630@sss.pgh.pa.us>
 <CAKJS1f9wbS+SzEDUXyMuLCsNgwMH=1Ztj3QE3WxuKdJtbqrOEA@mail.gmail.com>
 <13d143dd-0a44-0c16-218c-bc924b5922f0@2ndQuadrant.com>
 <26656.1552096451@sss.pgh.pa.us>
Comments: In-reply-to Tom Lane <tgl@sss.pgh.pa.us>
	message dated "Fri, 08 Mar 2019 20:54:11 -0500"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <28719.1552101086.1@sss.pgh.pa.us>
Date: Fri, 08 Mar 2019 22:11:26 -0500
Message-ID: <28720.1552101086@sss.pgh.pa.us>
Precedence: bulk

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