Re: PostgreSQL 9.2 high replication lag

‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐
On Thursday, July 15th, 2021 at 10:34 PM, Lucas <root(at)sud0(dot)nz> wrote:

> ‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐
>

> On Wednesday, July 14th, 2021 at 5:30 PM, Laurenz Albe laurenz(dot)albe(at)cybertec(dot)at wrote:
>

> On Tue, 2021-07-13 at 20:14 -0700, David G. Johnston wrote:
>

> > > On Tue, Jul 13, 2021 at 8:01 PM Lucas root(at)sud0(dot)nz wrote:
> > >

> > > > According to the documentation, max_standby_streaming_delay is a configuration parameter
> > > >

> > > > determining how long a standby server should wait before canceling queries that conflict
> > > >

> > > > with pending WAL entries received via streaming replication.
> > > >

> > > > My question then is: Which queries, if the slave can only receive SELECTs?
> > >

> > > The select queries...
> > >

> > > Follow the link in the docs for max_stanbdy_steaming_delay to:
> > >

> > > https://www.postgresql.org/docs/current/hot-standby.html#HOT-STANDBY-CONFLICT
> > >

> > > for the details.
> >

> > Perhaps you might find the following article interesting, where I tried to
> >

> > discuss this topic in some depth:
> >

> > https://www.cybertec-postgresql.com/en/streaming-replication-conflicts-in-postgresql/
>

> Thanks!
>

> I have changed max_standby_streaming_delay from -1 to 120s. However, I can still see the replication being delayed for up to 3 minutes quite frequent.
>

> I will try to see if I can find what kind of query is causing that... Maybe via pg_stat_database_conflicts?

[Screen Shot 2021-07-16 at 10.32.07 AM.png]

At 10pm (red mark) yesterday I changed the max_standby_streaming_delay = -1 to max_standby_streaming_delay = 120s.

However, it hasn't changed and the replication lag is still high all the time.

I'm deploying a new slave server but it will be in the same AZ as its master. And will monitor there and replication lag.

Please, let me know if you guys have any other suggestions.