Re: Streaming Replication delay getting bigger

On 06/12/2016 04:20, Patrick B wrote:
> Hi guys,
>
> I've got some database servers in USA (own data center) and also @ AWS Japan.
>
> *USA:*
> master01
> slave01 (Streaming Replication from master01 + wal_files)
> slave02 (Streaming Replication from master01 + wal_files)
>
> *Japan: (Cascading replication)*
> slave03 (Streaming Replication from slave02 + wal_files)
> slave04 (Streaming Replication from slave02)
>
> *Running this query on slave02:*
>
> select now() - pg_last_xact_replay_timestamp() AS replication_delay;
> replication_delay
> -------------------
> 00:00:00.802012
> (1 row)
>
> *Same query on slave03 and slave04:*
>
> select now() - pg_last_xact_replay_timestamp() AS replication_delay;
> replication_delay
> -------------------
> 00:56:53.639516
> (1 row)
>
>
> *slave02:*
>
> SELECT client_hostname , client_addr , pg_xlog_location_diff(pg_stat_replication.sent_location, pg_stat_replication.replay_location) AS byte_lag FROM pg_stat_replication;
> client_hostname | client_addr | byte_lag
> -----------------+---------------+----------
>
> | slave03 | 2097400
>
> | slave04 | 3803888
>
> (2 rows)
>
>
> Why is that delay that big? Is it because networking issue? I tried to find out what the cause is, but couldn't find anything.
>
> SCP and FTP (big files) between those servers are really fast, +1.0MB/s.

Are you sure the upstream does not produce WAL activity at a higher rate than 0.5MB/s ?

> I'm using PostgreSQL 9.2.14
>
> Thanks!
> Patrick.

--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt