Re: Throttling WAL inserts when the standby falls behind more than the configured replica_lag_in_bytes

On Tue, Jan 11, 2022 at 2:11 PM Konstantin Knizhnik <knizhnik(at)garret(dot)ru> wrote:
>
> We have faced with the similar problem in Zenith (open source Aurora)
> and have to implement back pressure mechanism to prevent overflow of WAL
> at stateless compute nodes
> and too long delays of [age reconstruction. Our implementation is the
> following:
> 1. Three GUCs are added: max_replication_write/flush/apply_lag
> 2. Replication lags are checked in XLogInsert and if one of 3 thresholds
> is reached then InterruptPending is set.
> 3. In ProcessInterrupts we block backend execution until lag is within
> specified boundary:
>
> #define BACK_PRESSURE_DELAY 10000L // 0.01 sec
> while(true)
> {
> ProcessInterrupts_pg();
>
> // Suspend writers until replicas catch up
> lag = backpressure_lag();
> if (lag <= 0)
> break;
>
> set_ps_display("backpressure throttling");
>
> elog(DEBUG2, "backpressure throttling: lag %lu", lag);
> pg_usleep(BACK_PRESSURE_DELAY);
> }
>
> What is wrong here is that backend can be blocked for a long time
> (causing failure of client application due to timeout expiration) and
> hold acquired locks while sleeping.

Do we ever call CHECK_FOR_INTERRUPTS() while holding "important"
locks? I haven't seen any asserts or anything of that sort in
ProcessInterrupts() though, looks like it's the caller's
responsibility to not process interrupts while holding heavy weight
locks, here are some points on this upthread [1].

I don't think we have problem with various postgres timeouts
statement_timeout, lock_timeout, idle_in_transaction_session_timeout,
idle_session_timeout, client_connection_check_interval, because while
we wait for replication lag to get better in ProcessInterrupts(). I
think SIGALRM can be raised while we wait for replication lag to get
better, but it can't be handled. Why can't we just disable these
timeouts before going to wait and reset/enable right after the
replication lag gets better?

And the clients can always have their own
no-reply-kill-transaction-sort-of-timeout, if yes, let them fail and
deal with it. I don't think we can do much about this.

> We are thinking about smarter way of choosing throttling delay (for
> example exponential increase of throttling sleep interval until some
> maximal value is reached).
> But it is really hard to find some universal schema which will be good
> for all use cases (for example in case of short living session, which
> clients are connected to the server to execute just one query).

I think there has to be an upper limit to wait, perhaps a
'preconfigured amount of time'. I think others upthread aren't happy
with failing transactions because of the replication lag. But, my
point is how much time we would let the backends wait or throttle WAL
writes? It mustn't be forever (say if a broken connection to the async
standby is found).

[1] https://www.postgresql.org/message-id/20220105174643.lozdd3radxv4tlmx%40alap3.anarazel.de

Regards,
Bharath Rupireddy.