1

I am using PostgreSQL 10.1 on MAC on which I am trying to set up streaming replication. I configured both master and slave to be on the same machine. I find the streaming replication lag to be slower than expected on mac. The same test runs on a Linux Ubuntu 16.04 machine without much lag.

I have the following insert script. 

for i in $(seq 1 1 1000) do bin/psql postgres -p 8999 -c "Insert into $1 select tz, $i * 127361::bigint, $i::real, random()*12696::bigint from generate_series('01-01-2018'::timestamptz, '02-01-2018'::timestamptz, '30 sec'::interval)tz;" echo $i done

The lag is measured using the following queries,

SELECT pg_last_wal_receive_lsn() - pg_last_wal_replay_lsn(); SELECT (extract(epoch FROM now()) - extract(epoch FROM pg_last_xact_replay_timestamp()))::int;

However, the observation is very unexpected. The lag is increasing from the moment the transactions are started on master. 

Slave localhost_9001: 12680304 1 Slave localhost_9001: 12354168 1 Slave localhost_9001: 16086800 1 . . . Slave localhost_9001: 3697460920 121 Slave localhost_9001: 3689335376 122 Slave localhost_9001: 3685571296 122 . . . . Slave localhost_9001: 312752632 190 Slave localhost_9001: 308177496 190 Slave localhost_9001: 303548984 190 . . Slave localhost_9001: 22810280 199 Slave localhost_9001: 8255144 199 Slave localhost_9001: 4214440 199 Slave localhost_9001: 0 0

It took around 4.5 minutes for a single client inserting on a single table to complete on master and another 4 minutes for the slave to catch up. Note that NO simultaneous selects are run other than the script to measure the lag.

I understand that replay in PostgreSQL is pretty simple like, "move a particular block to a location", but I am not sure about this behavior.

I have the following other configurations,

checkpoint_timeout = 5min max_wal_size = 1GB min_wal_size = 80MB

Now, I run the same tests with same configurations on a Linux Ubuntu 16.04 machine, I find the lag perfectly reasonable.

Am I missing anything?

UPDATE :

MAC Hardware configurations : 16GB RAM, 512GB SSD (Nvme) Intel(R) Core(TM) i7-7820HQ CPU @ 2.90GHz

Linux hardware configurations : 16GB RAM, 512GB SSD (Nvme) Intel(R) Core(TM) i7-6600U CPU @ 2.60GHz

Improve this question
1
  • Hi, as a Postgres expert, I always advise against using Postgres on another OS than Linux for several reasons (performance amongst them). You don't tell a thing about hardware on both machines (MAC and Linux). I suppose they're not the same... And did you compare performance parameters between the two (shared_buffers,effective_cache_size,work_mem... and autovacuum settings) ? Commented Dec 18, 2018 at 9:14

1 Answer 1

Reset to default
0

You can checked max_standby_streaming_delay (30s default) or max_standby_archive_delay (30s default) and recovery_min_apply_delay and checkpoint_flush_after (32 default).

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.