Re: Speedup twophase transactions

From: Stas Kelvich <s(dot)kelvich(at)postgrespro(dot)ru>
To: Michael Paquier <michael(dot)paquier(at)gmail(dot)com>
Cc: PostgreSQL mailing lists <pgsql-hackers(at)postgresql(dot)org>, Jesper Pedersen <jesper(dot)pedersen(at)redhat(dot)com>, Robert Haas <robertmhaas(at)gmail(dot)com>, Simon Riggs <simon(at)2ndquadrant(dot)com>
Subject: Re: Speedup twophase transactions
Date: 2016-04-13 14:31:25
Message-ID: 7C33A13C-5568-454B-B9B8-D025D219C2EA@postgrespro.ru
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers

> On 13 Apr 2016, at 01:04, Michael Paquier <michael(dot)paquier(at)gmail(dot)com> wrote:
>
> On Wed, Apr 13, 2016 at 1:53 AM, Stas Kelvich <s(dot)kelvich(at)postgrespro(dot)ru> wrote:
>>> On 12 Apr 2016, at 15:47, Michael Paquier <michael(dot)paquier(at)gmail(dot)com> wrote:
>>>
>>> It looks to be the case... The PREPARE phase replayed after the
>>> standby is restarted in recovery creates a series of exclusive locks
>>> on the table created and those are not taken on HEAD. Once those are
>>> replayed the LOCK_STANDBY record is conflicting with it. In the case
>>> of the failure, the COMMIT PREPARED record cannot be fetched from
>>> master via the WAL stream so the relation never becomes visible.
>>
>> Yep, it is. It is okay for prepared xact hold a locks for created/changed tables,
>> but code in standby_redo() was written in assumption that there are no prepared
>> xacts at the time of recovery. I’ll look closer at checkpointer code and will send
>> updated patch.
>>
>> And thanks again.
>
> That's too late for 9.6 unfortunately, don't forget to add that in the next CF!

Fixed patch attached. There already was infrastructure to skip currently
held locks during replay of standby_redo() and I’ve extended that with check for
prepared xids.

The reason why I’m still active on this thread is because I see real problems
in deploying 9.6 in current state. Let me stress my concern: current state of things
_WILL_BREAK_ async replication in case of substantial load of two phase
transactions on master. And a lot of J2EE apps falls into that category, as they
wrap most of their transactions in prepare/commit. Slave server just will always
increase it lag and will never catch up. It is possible to deal with that by switching
to synchronous replication or inserting triggers with pg_sleep on master, but it
doesn’t looks like normal behaviour of system.

Attachment Content-Type Size
twophase_replay.v5.patch application/octet-stream 30.6 KB
unknown_filename text/plain 95 bytes

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Robert Haas 2016-04-13 14:32:58 Re: Re: [COMMITTERS] pgsql: Avoid extra locks in GetSnapshotData if old_snapshot_threshold <
Previous Message Tatsuo Ishii 2016-04-13 14:30:24 Re: Incomplete startup packet errors