0

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

bumped to the homepage by Community 14 hours ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

  • 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) ? – Arkhena Dec 18 '18 at 9:14
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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.