Re: Streaming Replication delay getting bigger

[Achilleas Mantzios <achill@xxxxxxxxxxxxxxxxxxxxx>]

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