Postgresql streaming replication slow on macOSStreaming Replication in PostgreSQLpostgreSQL 9.3 streaming...
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
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
asked Dec 17 '18 at 14:30
krithikaGopalakrisnankrithikaGopalakrisnan
13619
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
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
asked Dec 17 '18 at 14:30
krithikaGopalakrisnankrithikaGopalakrisnan
13619
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
postgresql replication postgresql-10 master-slave-replication
asked Dec 17 '18 at 14:30
krithikaGopalakrisnankrithikaGopalakrisnan
13619
asked Dec 17 '18 at 14:30
krithikaGopalakrisnankrithikaGopalakrisnan
13619
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
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 |
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
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
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 |
1 Answer
1
active
oldest
votes
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)
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
});
}
});
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
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
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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)
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)
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)
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.
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)
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
answered 3 mins ago
Dhirendra PatilDhirendra Patil
1
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.
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.
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 |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
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
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
Required, but never shown
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
Required, but never shown
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
Required, but never shown
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
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
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