pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedWhat causes streaming replication delay?
3+ messages / 2 participants
[nested] [flat]
* What causes streaming replication delay?
@ 2016-08-31 15:44 Dianne Skoll <dfs@roaringpenguin.com>
2016-08-31 16:04 ` Re: What causes streaming replication delay? Keith <keith@keithf4.com>
0 siblings, 1 reply; 3+ messages in thread
From: Dianne Skoll @ 2016-08-31 15:44 UTC (permalink / raw)
To: pgsql-admin
Hi,
I have a problem that's driving me insane. I have two PostgreSQL 9.1
installations (yes, I know... very old.) One is configured as a master
and the other is a streaming-replication hot-standby that supports
read-only queries.
Every now and then, although the standby claims to be streaming transactions
if you look with "ps", it seems to stall and the replication delay grows
by one second for each second of elapsed time.
If I restart the hot-standby, it consumes WAL files and very quickly
catches up. And then a few minutes later, it happens again.
So, what are the possible causes of this behaviour? There are no
long transactions on the standby, nor on the master. And how can
I find out exactly what query is causing the problem, assuming
it's a query?
The relevant settings in the hot-standby postgresql.conf are:
hot_standby = on
max_standby_archive_delay = 10s
max_standby_streaming_delay = 10s
hot_standby_feedback = on
and on the master:
wal_level = hot_standby
archive_mode = on
archive_command = (a command to send WAL files to the standby)
max_wal_senders = 8
and all other parameters are at their defaults.
Regards,
Dianne.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: What causes streaming replication delay?
2016-08-31 15:44 What causes streaming replication delay? Dianne Skoll <dfs@roaringpenguin.com>
@ 2016-08-31 16:04 ` Keith <keith@keithf4.com>
2016-08-31 17:16 ` Re: What causes streaming replication delay? Dianne Skoll <dfs@roaringpenguin.com>
0 siblings, 1 reply; 3+ messages in thread
From: Keith @ 2016-08-31 16:04 UTC (permalink / raw)
To: Dianne Skoll <dfs@roaringpenguin.com>; +Cc: pgsql-admin
On Wed, Aug 31, 2016 at 11:44 AM, Dianne Skoll <dfs@roaringpenguin.com>
wrote:
> Hi,
>
> I have a problem that's driving me insane. I have two PostgreSQL 9.1
> installations (yes, I know... very old.) One is configured as a master
> and the other is a streaming-replication hot-standby that supports
> read-only queries.
>
> Every now and then, although the standby claims to be streaming
> transactions
> if you look with "ps", it seems to stall and the replication delay grows
> by one second for each second of elapsed time.
>
> If I restart the hot-standby, it consumes WAL files and very quickly
> catches up. And then a few minutes later, it happens again.
>
> So, what are the possible causes of this behaviour? There are no
> long transactions on the standby, nor on the master. And how can
> I find out exactly what query is causing the problem, assuming
> it's a query?
>
> The relevant settings in the hot-standby postgresql.conf are:
>
> hot_standby = on
> max_standby_archive_delay = 10s
> max_standby_streaming_delay = 10s
> hot_standby_feedback = on
>
> and on the master:
>
> wal_level = hot_standby
> archive_mode = on
> archive_command = (a command to send WAL files to the standby)
> max_wal_senders = 8
>
> and all other parameters are at their defaults.
>
> Regards,
>
> Dianne.
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>
All depends on how you're monitoring your slave lag. Check this blog post
and if you're seeing actual byte lag, then you could have cause for concern.
https://www.keithf4.com/monitoring_streaming_slave_lag/
Keith
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: What causes streaming replication delay?
2016-08-31 15:44 What causes streaming replication delay? Dianne Skoll <dfs@roaringpenguin.com>
2016-08-31 16:04 ` Re: What causes streaming replication delay? Keith <keith@keithf4.com>
@ 2016-08-31 17:16 ` Dianne Skoll <dfs@roaringpenguin.com>
0 siblings, 0 replies; 3+ messages in thread
From: Dianne Skoll @ 2016-08-31 17:16 UTC (permalink / raw)
To: pgsql-admin
On Wed, 31 Aug 2016 12:04:23 -0400
Keith <keith@keithf4.com> wrote:
> All depends on how you're monitoring your slave lag. Check this blog
> post and if you're seeing actual byte lag, then you could have cause
> for concern.
It's not because the master is idle... there is tons of write traffic.
And the way I measure the lag is about as directly as you can: I
periodically insert a timestamp into a specific table on the master,
and then poll the hot-standby until the inserted timestamp has
appeared.
Regards,
Dianne.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2016-08-31 17:16 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-08-31 15:44 What causes streaming replication delay? Dianne Skoll <dfs@roaringpenguin.com>
2016-08-31 16:04 ` Keith <keith@keithf4.com>
2016-08-31 17:16 ` Dianne Skoll <dfs@roaringpenguin.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox