Re: WAL insert delay settings

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.

> 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.

cheers

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