Re: WAL insert delay settings

On February 14, 2019 10:31:57 AM GMT+01:00, Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com> wrote:
>
>
>On 2/14/19 10:06 AM, Andres Freund wrote:
>> Hi,
>>
>> On 2019-02-14 10:00:38 +0100, Tomas Vondra wrote:
>>> On 2/13/19 4:31 PM, Stephen Frost wrote:
>>>> Greetings,
>>>>
>>>> * Peter Eisentraut (peter(dot)eisentraut(at)2ndquadrant(dot)com) wrote:
>>>>> Bulk operations like CREATE INDEX, ALTER TABLE, or bulk loads can
>create
>>>>> a lot of WAL. A lot of WAL at once can cause delays in
>replication.
>>>>
>>>> Agreed, though I think VACUUM should certainly be included in this.
>>>>
>>>
>>> Won't these two throttling criteria interact in undesirable and/or
>>> unpredictable way? With the regular vacuum throttling (based on
>>> hit/miss/dirty) it's possible to compute rough read/write I/O
>limits.
>>> But with the additional sleeps based on amount-of-WAL, we may sleep
>for
>>> one of two reasons, so we may not reach either limit. No?
>>
>> Well, it'd be max rates for either, if done right. I think we only
>> should start adding delays for WAL logging if we're exceeding the WAL
>> write rate.
>
>Not really, I think. If you add additional sleep() calls somewhere,
>that
>may affect the limits in vacuum, making it throttle before reaching the
>derived throughput limits.

I don't understand. Obviously, if you have two limits, the scarcer resource can limit full use of the other resource. That seems OK? The thing u think we need to be careful about is not to limit in a way, e.g. by adding sleeps even when below the limit, that a WAL limit causes throttling of normal IO before the WAL limit is reached.

>> That's obviously more complicated than the stuff we do for
>> the current VACUUM throttling, but I can't see two such systems
>> interacting well. Also, the current logic just doesn't work well when
>> you consider IO actually taking time, and/or process scheduling
>effects
>> on busy systems.
>>
>
>True, but making it even less predictable is hardly an improvement.

I don't quite see the problem here. Could you expand?

Andres
--
Sent from my Android device with K-9 Mail. Please excuse my brevity.