Re: WAL insert delay settings

On 2/20/19 10:43 PM, Stephen Frost wrote:
> Greetings,
> ...
>>
>> 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.
>
> Well, it seems like Andres explained how it'll work with the existing
> throttling, no? As for how all of this will work in the end, that's a
> good question but also a rather difficult one to answer, I suspect.
>

Well ... he explained how to do WAL throttling, and I agree what he
proposed seems entirely sane to me.

But when it comes to interactions with current vacuum cost-based
throttling, he claims it does not get meaningfully more confusing due to
interactions with WAL throttling. I don't quite agree with that, but I'm
not going to beat this horse any longer ...

> Just to share a few additional thoughts after pondering this for a
> while, but the comment Andres made up-thread really struck a chord- we
> don't necessairly want to throttle anything, what we'd really rather do
> is *prioritize* things, whereby foreground work (regular queries and
> such) have a higher priority than background/bulk work (VACUUM, REINDEX,
> etc) but otherwise we use the system to its full capacity. We don't
> actually want to throttle a VACUUM run any more than a CREATE INDEX, we
> just don't want those to hurt the performance of regular queries that
> are happening.
>

I think you're forgetting the motivation of this very patch was to
prevent replication lag caused by a command generating large amounts of
WAL (like CREATE INDEX / ALTER TABLE etc.). That has almost nothing to
do with prioritization or foreground/background split.

I'm not arguing against ability to prioritize stuff, but I disagree it
somehow replaces throttling.

> The other thought I had was that doing things on a per-table basis, in
> particular, isn't really addressing the resource question appropriately.
> WAL is relatively straight-forward and independent of a resource from
> the IO for the heap/indexes, so getting an idea from the admin of how
> much capacity they have for WAL makes sense. When it comes to the
> capacity for the heap/indexes, in terms of IO, that really goes to the
> underlying storage system/channel, which would actually be a tablespace
> in properly set up environments (imv anyway).
>
> Wrapping this up- it seems unlikely that we're going to get a
> priority-based system in place any time particularly soon but I do think
> it's worthy of some serious consideration and discussion about how we
> might be able to get there. On the other hand, if we can provide a way
> for the admin to say "these are my IO channels (per-tablespace values,
> plus a value for WAL), here's what their capacity is, and here's how
> much buffer for foreground work I want to have (again, per IO channel),
> so, PG, please arrange to not use more than 'capacity-buffer' amount of
> resources for background/bulk tasks (per IO channel)" then we can at
> least help them address the issue that foreground tasks are being
> stalled or delayed due to background/bulk work. This approach means
> that they won't be utilizing the system to its full capacity, but
> they'll know that and they'll know that it's because, for them, it's
> more important that they have that low latency for foreground tasks.
>

I think it's mostly orthogonal feature to throttling.

regards

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