Thread: Streaming Replication delay getting bigger
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:
Same query on slave03 and slave04:select now() - pg_last_xact_replay_timestamp() AS replication_delay;replication_delay-------------------00:00:00.802012(1 row)
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.
I'm using PostgreSQL 9.2.14
Thanks!
Patrick.
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:master01slave01 (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:Same query on slave03 and slave04:select now() - pg_last_xact_replay_timestamp() AS replication_delay;replication_delay-------------------00:00:00.802012(1 row)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.14Thanks!Patrick.
-- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt