Re: SAVEPOINTs and COMMIT performance

Did this ever get addressed?

---------------------------------------------------------------------------

Simon Riggs wrote:
>
> As part of a performance investigation for a customer I've noticed an
> O(N^2) performance issue on COMMITs of transactions that contain many
> SAVEPOINTs. I've consistently measured COMMIT times of around 9 seconds,
> with 49% CPU, mostly in LockReassignCurrentOwner().
>
> BEGIN;
> INSERT...
> SAVEPOINT ...
> INSERT...
> SAVEPOINT ...
> ... (repeat 10,000 times)
> COMMIT;
>
> The way SAVEPOINTs work is that each is nested within the previous one,
> so that at COMMIT time we must recursively commit all the
> subtransactions before we issue final commit.
>
> That's a shame because ResourceOwnerReleaseInternal() contains an
> optimisation to speed up final commit, by calling ProcReleaseLocks().
>
> What we actually do is recursively call LockReassignCurrentOwner() which
> sequentially scans LockMethodLocalHash at each level of transaction. The
> comments refer to this as "retail" rather than the wholesale method,
> which never gets to execute anything worthwhile in this case.
>
> This issue does NOT occur in PLpgSQL functions that contain many
> EXCEPTION clauses in a loop, since in that case the subtransactions are
> started and committed from the top level so that the subxact nesting
> never goes too deep.
>
> Fix looks like we need special handling for the depth-first case, rather
> than just a recursion loop in CommitTransactionCommand().
>
> Issues looks like it goes all the way back, no fix for 9.0.
>
> I notice also that the nesting model of SAVEPOINTs also means that
> read-only subtransactions will still generate an xid when followed by a
> DML statement. That's unnecessary, but required given current design.
>
> --
> Simon Riggs www.2ndQuadrant.com
> PostgreSQL Development, 24x7 Support, Training and Services
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +