Message-ID: <54EF3E6F.60403@gmail.com>
Date: Thu, 26 Feb 2015 10:40:31 -0500
From: John Scalia <jayknowsunix@gmail.com>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64;
 rv:31.0) Gecko/20100101 Thunderbird/31.4.0
MIME-Version: 1.0
To: "David F. Skoll" <dfs@roaringpenguin.com>,
 pgsql-admin@postgresql.org
Subject: Re: Weird spikes in delay for async streaming replication
 on 9.1
References: <20150214110050.2f032f05@ollie.roaringpenguin.com>
 <20150226094428.34531376@hydrogen.roaringpenguin.com>
In-Reply-To: <20150226094428.34531376@hydrogen.roaringpenguin.com>
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org


On 2/26/2015 9:44 AM, David F. Skoll wrote:
> 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
> [...]

Apologies, David, but as I don't check this list very often, I thought you should have received an answer. Have you tried watching the transaction with tcpdump or wireshark? To me 
this sounds like either a network problem or like you're thinking a contention problem. Also, are you running streaming replication? If so, there shouldn't be ANY delay as the 
standby gets the commit before the primary. Otherwise, how are you replicating?
--
Jay


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