Message-ID: <55CE1898.10605@agliodbs.com>
Date: Fri, 14 Aug 2015 09:34:32 -0700
From: Josh Berkus <josh@agliodbs.com>
Organization: PostgreSQL Experts Inc.
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:31.0) Gecko/20100101 Thunderbird/31.8.0
MIME-Version: 1.0
To: Kevin Grittner <kgrittn@ymail.com>,
 "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>,
 Jeff Janes <jeff.janes@gmail.com>
Subject: Re: Strange query stalls on replica in 9.3.9
References: <55CCCF4A.2080102@agliodbs.com>
 <512125623.2442595.1439497468170.JavaMail.yahoo@mail.yahoo.com>
 <WM!4bf9ac0987e78f17855daad8c1117172fc8af60ae68a3a6d5ba3c58fe973c992347e087808be13ef8136a37aa63a101e!@asav-3.01.com>
In-Reply-To: 
 <WM!4bf9ac0987e78f17855daad8c1117172fc8af60ae68a3a6d5ba3c58fe973c992347e087808be13ef8136a37aa63a101e!@asav-3.01.com>
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

On 08/13/2015 01:24 PM, Kevin Grittner wrote:
>> Thing is, the update all rows only takes 2.5 seconds to execute on the
>> master. So even if the update is blocking the seq scans on the replica
>> (and I can't see why it would), it should only block them for < 3 seconds.
> 
> Visibility hinting and/or hot pruning?

Unlikely; I can VACUUM FULL the entire database in 30 seconds.  This
database is small.  Jeff's answer seems more likely ...

On 08/13/2015 01:59 PM, Jeff Janes wrote: execute on the

> Once the commit of the whole-table update has replayed, the problem
> should go way instantly because at that point each backend doing the
> seqscan will find the the transaction has committed and so will set the
> hint bit that means all of the other seqscan backends that come after it
> can skip the proc array scan for that tuple.

Yes ... and given that the commit on the master took < 3 seconds, it's
not likely to take 30 seconds on the replica.  That aside, the pattern
of behavior does look similar to the planner issue.

> So perhaps the commit of the whole-table update is delayed because the
> startup process as also getting bogged down on the same contended lock?
> I don't know how hard WAL replay hits the proc array lock.

I don't know; we don't have any visibility into the replay process, and
no way to tell if replay is waiting on some kind of lock.  A regular
UPDATE should not block against any select activity on the replay, though.

Also, why would this affect *only* the query which does seq scans?  Is
there some difference between seqscan and index scan here, or is it
simply because they take longer, and since this issue is timing-based,
they're more likely to be hit?  Significantly, the seqscan query is also
the most complex query run against the replica, so maybe the seqscan is
irrelevant and it's being affected by planner issues?

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com


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