From: Andres Freund <andres@2ndquadrant.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Jeff Frost <jeff@pgexperts.com>
Cc: Matheus de Oliveira <matioli.matheus@gmail.com>
Cc: Soni M <diptatapa@gmail.com>
Cc: Heikki Linnakangas <hlinnakangas@vmware.com>
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: Postgres Replaying WAL slowly
Date: Wed, 2 Jul 2014 21:01:11 +0200
Message-ID: <20140702190111.GC25909@awork2.anarazel.de> (raw)
In-Reply-To: <18433.1404242437@sss.pgh.pa.us>
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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
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: andres@2ndquadrant.com, tgl@sss.pgh.pa.us, jeff@pgexperts.com, matioli.matheus@gmail.com, diptatapa@gmail.com, hlinnakangas@vmware.com
Subject: Re: Postgres Replaying WAL slowly
In-Reply-To: <20140702190111.GC25909@awork2.anarazel.de>
* 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