From: Josh Berkus <josh@agliodbs.com>
To: Kevin Grittner <kgrittn@ymail.com>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
To: Jeff Janes <jeff.janes@gmail.com>
Subject: Re: Strange query stalls on replica in 9.3.9
Date: Fri, 14 Aug 2015 09:34:32 -0700
Message-ID: <55CE1898.10605@agliodbs.com> (raw)
In-Reply-To: <WM!4bf9ac0987e78f17855daad8c1117172fc8af60ae68a3a6d5ba3c58fe973c992347e087808be13ef8136a37aa63a101e!@asav-3.01.com>
References: <55CCCF4A.2080102@agliodbs.com>
<512125623.2442595.1439497468170.JavaMail.yahoo@mail.yahoo.com>
<WM!4bf9ac0987e78f17855daad8c1117172fc8af60ae68a3a6d5ba3c58fe973c992347e087808be13ef8136a37aa63a101e!@asav-3.01.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: josh@agliodbs.com, kgrittn@ymail.com, jeff.janes@gmail.com
Subject: Re: Strange query stalls on replica in 9.3.9
In-Reply-To: <55CE1898.10605@agliodbs.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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