Re: WAL insert delay settings

On 2/19/19 7:28 PM, Robert Haas wrote:
> On Fri, Feb 15, 2019 at 1:42 PM Andres Freund <andres(at)anarazel(dot)de> wrote:
>> I think it'd not be insane to add two things:
>> - WAL write rate limiting, independent of the vacuum stuff. It'd also be
>> used by lots of other bulk commands (CREATE INDEX, ALTER TABLE
>> rewrites, ...)
>> - Account for WAL writes in the current vacuum costing logic, by
>> accounting for it using a new cost parameter
>>
>> Then VACUUM would be throttled by the *minimum* of the two, which seems
>> to make plenty sense to me, given the usecases.
>
> Or maybe we should just blow up the current vacuum cost delay stuff
> and replace it with something that is easier to tune. For example, we
> could just have one parameter that sets the maximum read rate in kB/s
> and another that sets the maximum dirty-page rate in kB/s. Whichever
> limit is tighter binds. If we also have the thing that is the topic
> of this thread, that's a third possible upper limit.
>
> I really don't see much point in doubling down on the current vacuum
> cost delay logic. The overall idea is good, but the specific way that
> you have to set the parameters is pretty inscrutable, and I think we
> should just fix it so that it can be, uh, scruted.
>

I think changing the vacuum throttling so that it uses actual I/O
amounts (in kB/s) instead of the cost limit would be a step in the right
directly. It's clearer, and it also works with arbitrary page sizes.

Then, instead of sleeping for a fixed amount of time after reaching the
cost limit, we should track progress and compute the amount of time we
actually need to sleep. AFAICS that's what spread checkpoints do.

I'm sure it's bound to be trickier in practice, of course.

FWIW I'm not entirely sure we want to fully separate the limits. I'd
argue using the same vacuum budget for both reads and writes is actually
the right thing to do - the I/O likely hits the same device. So it makes
sense to "balance" those two in some way. But that may or may not be the
case for WAL, which is often moved to a different device.

regards

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