Re: SAVEPOINTs and COMMIT performance - Mailing list pgsql-hackers

What happened to this patch?

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

Simon Riggs wrote:
> On Sun, 2011-02-06 at 12:11 -0500, Bruce Momjian wrote:
> > Did this ever get addressed?
> 
> Patch attached.
> 
> Seems like the easiest fix I can come up with.
> 
> > 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@postgresql.org)
> > > To make changes to your subscription:
> > > http://www.postgresql.org/mailpref/pgsql-hackers
> > 
> 
> -- 
>  Simon Riggs           http://www.2ndQuadrant.com/books/
>  PostgreSQL Development, 24x7 Support, Training and Services
>  

[ Attachment, skipping... ]

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + It's impossible for everything to be true. +