Postgresql streaming replication slow on macOS

1 vote

1 answer

249 views

postgresql replication postgresql-10 master-slave-replication

                          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
                        

Asked by krithikaGopalakrishnan (181 rep)

Dec 17, 2018, 02:30 PM
Last activity: May 24, 2025, 08:05 PM

Postgresql streaming replication slow on macOS

Related Questions