Re: WAL insert delay settings

On 2/19/19 8:40 PM, Andres Freund wrote:
> Hi,
>
> On 2019-02-19 20:34:25 +0100, Tomas Vondra wrote:
>> On 2/19/19 8:22 PM, Andres Freund wrote:
>>> And my main point is that even if you implement a proper bytes/sec limit
>>> ONLY for WAL, the behaviour of VACUUM rate limiting doesn't get
>>> meaningfully more confusing than right now.
>>>
>>
>> So, why not to modify autovacuum to also use this approach? I wonder if
>> the situation there is more complicated because of multiple workers
>> sharing the same budget ...
>
> I think the main reason is that implementing a scheme like this for WAL
> rate limiting isn't a small task, but it'd be aided by the fact that
> it'd probably not on by default, and that there'd not be any regressions
> because the behaviour didn't exist before. I contrast, people are
> extremely sensitive to autovacuum behaviour changes, even if it's to
> improve autovacuum. I think it makes more sense to build the logic in a
> lower profile case first, and then migrate autovacuum over it. Even
> leaving the maturity issue aside, reducing the scope of the project into
> more bite sized chunks seems to increase the likelihood of getting
> anything substantially.
>

Maybe.

I guess the main thing I'm advocating for here is to aim for a unified
throttling approach, not multiple disparate approaches interacting in
ways that are hard to understand/predict.

The time-based approach you described looks fine, an it's kinda what I
was imagining (and not unlike the checkpoint throttling). I don't think
it'd be that hard to tweak autovacuum to use it too, but I admit I have
not thought about it particularly hard and there's stuff like per-table
settings which might make it more complex.

So maybe doing it for WAL first makes sense. But I still think we need
to have at least a rough idea how it interacts with the existing
throttling and how it will work in the end.

regards

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