Re: WAL insert delay settings

On 2/19/19 7:50 PM, Andres Freund wrote:
> On 2019-02-19 19:43:14 +0100, Tomas Vondra wrote:
>>
>>
>> On 2/19/19 7:35 PM, Andres Freund wrote:
>>> Hi,
>>>
>>> On 2019-02-19 13:28:00 -0500, 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 agree that that's something worthwhile to do, but given that the
>>> proposal in this thread is *NOT* just about VACUUM, I don't see why it'd
>>> be useful to tie a general WAL rate limiting to rewriting cost limiting
>>> of vacuum. It seems better to write the WAL rate limiting logic with an
>>> eye towards structuring it in a way that'd potentially allow reusing
>>> some of the code for a better VACUUM cost limiting.
>>>
>>> I still don't *AT ALL* buy Stephen and Tomas' argument that it'd be
>>> confusing that when both VACUUM and WAL cost limiting are active, the
>>> lower limit takes effect.
>>>
>>
>> Except that's not my argument. I'm not arguing against throttling once
>> we hit the minimum of limits.
>>
>> The problem I have with implementing a separate throttling logic is that
>> it also changes the other limits (which are already kinda fuzzy). If you
>> add sleeps somewhere, those will affects the throttling built into
>> autovacuum (lowering them in some unknown way).
>
> Those two paragraphs, to me, flat out contradict each other. If you
> throttle according to the lower of two limits, *of course* the
> throttling for the higher limit is affected in the sense that the limit
> won't be reached. So yea, if you have a WAL rate limit, and you reach
> it, you won't be able to predict the IO rate for vacuum. Duh? I must be
> missing something here.
>

Let's do a short example. Assume the default vacuum costing parameters

vacuum_cost_limit = 200
vacuum_cost_delay = 20ms
cost_page_dirty = 20

and for simplicity we only do writes. So vacuum can do ~8MB/s of writes.

Now, let's also throttle based on WAL - once in a while, after producing
some amount of WAL we sleep for a while. Again, for simplicity let's
assume the sleeps perfectly interleave and are also 20ms. So we have
something like:

sleep(20ms); -- vacuum
sleep(20ms); -- WAL
sleep(20ms); -- vacuum
sleep(20ms); -- WAL
sleep(20ms); -- vacuum
sleep(20ms); -- WAL
sleep(20ms); -- vacuum
sleep(20ms); -- WAL

Suddenly, we only reach 4MB/s of writes from vacuum. But we also reach
only 1/2 the WAL throughput, because it's affected exactly the same way
by the sleeps from vacuum throttling.

We've not reached either of the limits. How exactly is this "lower limit
takes effect"?

regards

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