Date: Wed, 2 Jul 2014 21:01:11 +0200
From: Andres Freund <andres@2ndquadrant.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Jeff Frost <jeff@pgexperts.com>,
 Matheus de Oliveira <matioli.matheus@gmail.com>,
 Soni M <diptatapa@gmail.com>, Heikki Linnakangas <hlinnakangas@vmware.com>,
 pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: Postgres Replaying WAL slowly
Message-ID: <20140702190111.GC25909@awork2.anarazel.de>
References: <B22B06CF-2BC0-4AB6-8E3C-FACDF8193F23@pgexperts.com>
 <20140630201523.GV26930@awork2.anarazel.de>
 <943ECC8E-8899-422E-937C-50DE20E1F7AC@pgexperts.com>
 <21474.1404160784@sss.pgh.pa.us>
 <2E438E7D-E3C1-47A1-A1AF-9FE50A14A302@pgexperts.com>
 <571D044B-260A-4E06-ACBC-8869B8F73C0F@pgexperts.com>
 <24455.1404169460@sss.pgh.pa.us>
 <BB7C727F-201D-4CA7-AB00-8EDF6EBFD04B@pgexperts.com>
 <608DF51D-AEFB-422B-AD2C-ABA9A114AA27@pgexperts.com>
 <18433.1404242437@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <18433.1404242437@sss.pgh.pa.us>
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

On 2014-07-01 15:20:37 -0400, Tom Lane wrote:
> Jeff Frost <jeff@pgexperts.com> writes:
> >> On Jun 30, 2014, at 4:04 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >>> Did you check whether the locks were all on temp tables of the
> >>> ON COMMIT DROP persuasion?
> 
> > And indeed it did catch up overnight and the lag increased shortly after a correlating spike in AccessExclusiveLocks that were generated by temp table creation with on commit drop.
> 
> OK, so we have a pretty clear idea of where the problem is now.
> 
> It seems like there are three, not mutually exclusive, ways we might
> address this:
> 
> 1. Local revisions inside StandbyReleaseLocks to make it perform better in
> the presence of many locks.  This would only be likely to improve matters
> much if there's a fixable O(N^2) algorithmic issue; but there might well
> be one.
>
> It sounded like Andres had taken a preliminary look at #1 and found a
> possible avenue for improvement, which I'd encourage him to pursue.
> 

I don't have the resources to do this right now, but yes, I think we can
get relatively easily get rid of the O(num_locks * num_subtransactions)
behaviour.

> 2. Avoid WAL-logging AccessExclusiveLocks associated with temp tables, on
> the grounds that no standby should be touching them.  I'm not entirely
> sure that that argument is bulletproof though; in particular, even though
> a standby couldn't access the table's data, it's possible that it would be
> interested in seeing consistent catalog entries.

Hm. We definitely perform checks surprisingly late for those. It's
possible to do SELECT * FROM pg_temp_<nn>.whatever; without an error f
there's no rows of if the rest of the plan doesn't do accesses to that
table. The check prohibiting access is only in bufmgr.c...
So yea, I don't think we can do this for at least < 9.4. And there
it'll still be hard.

> 3. Avoid WAL-logging AccessExclusiveLocks associated with
> new-in-transaction tables, temp or not, on the grounds that no standby
> could even see such tables until they're committed.  We could go a bit
> further and not take out any locks on a new-in-transaction table in the
> first place, on the grounds that other transactions on the master can't
> see 'em either.
> 
> For both #2 and the conservative version of #3, the main implementation
> problem would be whether the lock WAL-logging code has cheap access to
> the necessary information.  I suspect it doesn't.

Not trivially. It's logged directly in LockAcquireExtended(). We could
add the information into locktags as there's unused fields for relation
locktags, but brrr.

> The radical version of #3 might be pretty easy to do, at least to the
> extent of removing locks taken out during CREATE TABLE.  I suspect there
> are some assertions or other consistency checks that would get unhappy if
> we manipulate relations without locks, though, so those would have to be
> taught about the exception.
>
> Also, we sometimes forget new-in-transaction
> status during relcache flush events; it's not clear if that would be a
> problem for this.

I think that hole is actually pluggable in newer releases - at least
there's no code around that assumes rd_createSubid now is persistent,
even across cache resets.

But I think more importantly it's probably quite possible to hit a
similar problem without ON COMMIT DROP relations. Say DISCARD TEMP
inside a transaction (with several subxacts) or so? So we probaly really
should fix the bad scaling.

Greetings,

Andres Freund

-- 
 Andres Freund	                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


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