Date: Thu, 26 Feb 2015 09:44:28 -0500
From: "David F. Skoll" <dfs@roaringpenguin.com>
To: pgsql-admin@postgresql.org
Subject: Re: Weird spikes in delay for async streaming replication on 9.1
Message-ID: <20150226094428.34531376@hydrogen.roaringpenguin.com>
In-Reply-To: <20150214110050.2f032f05@ollie.roaringpenguin.com>
References: <20150214110050.2f032f05@ollie.roaringpenguin.com>
Organization: Roaring Penguin Software Inc.
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

Hi,

I posted this a couple of weeks ago and no response... I guess it's
quite mysterious.

Anyway, behavior is consistent.  It seems that long-running
transactions on the primary (even read-only ones, because in my case
it's a pg_dump) can block subsequent transactions from appearing on
the hot-standby until the first transaction finishes.  Is this a known
limitation?  I run 9.1; is it still present in 9.4?

Regards,

David.

========================================================================
[...]

> I have a monitoring script that tests the actual delay for a
> transaction on the master to appear on the hot-standby.  Every few
> minutes, my script runs an update on the master and then sits in a
> loop checking how long it takes to appear on the hot-standby.  99% of
> the time, it's less than a second.

> But every once in a while, the time spikes dramatically, to hundreds
> or thousands of seconds, and that's too long... the delay-tolerant
> queries are not *that* delay-tolerant, so we switch to sending them
> all to the master.

> See the graph: http://ibin.co/1rdm4ekiWmpM

[...]


-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin