Sfrgttk

Humanity loses the vast majority of its technology, information, and population in the year 2122. How long does it take to rebuild itself?

Why must traveling waves have the same amplitude to form a standing wave?

Sword in the Stone story where the sword was held in place by electromagnets

Why do Australian milk farmers need to protest supermarkets' milk price?

Why did it take so long to abandon sail after steamships were demonstrated?

Why are there 40 737 Max planes in flight when they have been grounded as not airworthy?

Why would a flight no longer considered airworthy be redirected like this?

Welcoming 2019 Pi day: How to draw the letter π?

How could a female member of a species produce eggs unto death?

Why using two cd commands in bash script does not execute the second command

How to answer questions about my characters?

Can hydraulic brake levers get hot when brakes overheat?

Be in awe of my brilliance!

Pinhole Camera with Instant Film

Rules about breaking the rules. How do I do it well?

An Accountant Seeks the Help of a Mathematician

How to simplify this time periods definition interface?

Identifying the interval from A♭ to D♯

What is the greatest age difference between a married couple in Tanach?

Can the damage from a Talisman of Pure Good (or Ultimate Evil) be non-lethal?

Brexit - No Deal Rejection

RegionDifference for Cylinder and Cuboid

Did CPM support custom hardware using device drivers?

Replacing Windows 7 security updates with anti-virus?

Postgresql streaming replication slow on macOS

Streaming Replication in PostgreSQLpostgreSQL 9.3 streaming replication delayTransaction speed benchmarks for mySQL v5.6 replication - seems very slowPostgresql - Streaming replication - STONITHPostgreSQL SELECTs not returning correct result following recovery?PostgreSQL Streaming Replication Tools - WindowsPostgresql Streaming replication - monitoringpostgresql streaming replicationPostgresql Slow query on large table with GROUP BY and ORDER BYpostgresql 10.5 slow query issue only on MacOS

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

postgresql replication postgresql-10 master-slave-replication

share|improve this question

edited Dec 18 '18 at 9:59

krithikaGopalakrisnan

asked Dec 17 '18 at 14:30

krithikaGopalakrisnankrithikaGopalakrisnan

13619

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  

  
  
  
  

add a comment | 

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

postgresql replication postgresql-10 master-slave-replication

share|improve this question

edited Dec 18 '18 at 9:59

krithikaGopalakrisnan

asked Dec 17 '18 at 14:30

krithikaGopalakrisnankrithikaGopalakrisnan

13619

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  

  
  
  
  

add a comment | 

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

postgresql replication postgresql-10 master-slave-replication

share|improve this question

edited Dec 18 '18 at 9:59

krithikaGopalakrisnan

asked Dec 17 '18 at 14:30

krithikaGopalakrisnankrithikaGopalakrisnan

13619

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

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;

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  

checkpoint_timeout = 5min       

max_wal_size = 1GB

min_wal_size = 80MB

share|improve this question

edited Dec 18 '18 at 9:59

krithikaGopalakrisnan

asked Dec 17 '18 at 14:30

krithikaGopalakrisnankrithikaGopalakrisnan

13619

share|improve this question

edited Dec 18 '18 at 9:59

krithikaGopalakrisnan

asked Dec 17 '18 at 14:30

krithikaGopalakrisnankrithikaGopalakrisnan

13619

asked Dec 17 '18 at 14:30

krithikaGopalakrisnankrithikaGopalakrisnan

13619

asked Dec 17 '18 at 14:30

krithikaGopalakrisnankrithikaGopalakrisnan

13619

1 Answer
1

active

oldest

votes

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)

share

answered 3 mins ago

Dhirendra PatilDhirendra Patil

1

New contributor

Dhirendra Patil is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f225166%2fpostgresql-streaming-replication-slow-on-macos%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

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)

share

answered 3 mins ago

Dhirendra PatilDhirendra Patil

1

New contributor

Dhirendra Patil is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

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)

share

answered 3 mins ago

Dhirendra PatilDhirendra Patil

1

New contributor

Dhirendra Patil is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

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)

share

answered 3 mins ago

Dhirendra PatilDhirendra Patil

1

New contributor

Dhirendra Patil is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share

answered 3 mins ago

Dhirendra PatilDhirendra Patil

1

New contributor

Dhirendra Patil is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

answered 3 mins ago

Dhirendra PatilDhirendra Patil

1

New contributor

Dhirendra Patil is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

answered 3 mins ago

Dhirendra PatilDhirendra Patil

1